Modify WHERE clause in Stored Procedure
-
Is it possible to modify the WHERE clause in a stored procedure based on an input parameter?
For example;
CREATE PROCEDURE sp_Modify_Where
@Open bit = 1
AS
SELECT ID, TITLE FROM MyTable WHERE CLOSED_DATE IS ?
If @Open is 0 then the WHERE clause would be "CLOSED_DATE IS NULL" and if @Open is 1 then the WHERE clause would be "CLOSED_DATE IS NOT NULL"
Any suggestions would be greatly appreciated.
-
Probably the easiest solution would be to use something like
USE northwind
declare @a bit
set @a = 0
if @a = 0
select * from orders where shippeddate is null
else
select * from orders where shippeddate is not null
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks Frank however I should explain that I am already using this type of code with 2 dates that are passed in. So I already have;
IF @Start IS NOT NULL AND @End IS NOT NULL
BEGIN
...
END
IF @Start IS NULL AND @End IS NOT NULL
BEGIN
...
END
IF @Start IS NOT NULL AND @End IS NULL
BEGIN
...
END
IF @Start IS NULL AND @End IS NULL
BEGIN
...
END
So I am trying to avoid having to wrap all this in another set of IF statements.
-
you could build a dynamic SQL statement, and execute it with sp_executesql... however, this isn't very safe, as a user could input some sql commands and do something else to your database. ..... also the sql statement isn't optimised like a compiled sproc is.
DECLARE @SQL nvarchar(4000)
If @Start is null SET @sql = 'SELECT ID, TITLE FROM MyTable WHERE CLOSED_DATE IS NULL' ELSE SET @sql = 'SELECT ID, TITLE FROM MyTable WHERE CLOSED_DATE IS NOT NULL'
IF @end is null SET @sql = @SQL + ' AND OpenDate IS NULL' ELSE SET @sql = @SQL + ' AND OpenDate IS NOT NULL' exec sp_executesql @SQL
eww... please no one remind me I just gave an unsafe code example....
Julian Kuiters
juliankuiters.id.au -
Depending on what you want to show when one or both parameters is NULL, what about using default values like so:
use northwind
go
create procedure dbo.test_me
@a datetime ='19000101', @b-2 datetime = '20760601'
as
select * from orders where orderdate>=@a and orderdate<=@b
return 0
go
exec dbo.test_me '19960709'
drop procedure dbo.test_me
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I've often used temp tables to first construct the initial parameter set, then execute an SP which joins to the temp table. Your looking at at least two SPs, and if you're using business objects, you will need to explicitly create the connection object, create the temp table(s) against the connection object, and then run it.
Although it might sound a little involved, it's fast, effective, and if done correctly, facilitates modularity.
-
I would use a case statement in your SP.
SELECT ID, TITLE FROM MyTable
WHERE
Closed_Date
CASE @Open
WHEN 0 THEN
Is Null
ELSE
IS NOT NULL
Edward M. Sokolove -
Hm, can you show me how to get this running?
declare @open bit
set @open = 0
SELECT * FROM orders
WHERE
shippeddate
CASE @Open
WHEN 0 THEN
Is Null
ELSE
IS NOT NULL
END
Server: Nachr.-Nr. 156, Schweregrad 15, Status 1, Zeile 6
Falsche Syntax in der Nähe des CASE-Schlüsselwortes.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Try This...
CREATE PROCEDURE sp_Modify_Where
@Open bit = 1
AS
select
*
from
mytable
where
(@Open = 1 AND closed_date is null) OR
(@Open = 0 AND closed_date is not null)
Edwin Stephenraj.
-
Thanks Edwin - worked a treat
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply