optional parameter - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

optional parameter

Author

Topic

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-01-05 : 09:39:39

Is it possible to modify a single where clause based on a whether a parameter is passed or not without having to repeat the code in an if else?

IF LEN(@RequiredDate)=0
BEGIN
Select *
From MyTable A
Where A.title = @RequiredTitle
END
ELSE
BEGIN
Select *
From MyTable A
Where A.title = @RequiredTitle AND A.date = @RequiredDate
END

I have a long SQL statement, well longer than this and it seems inefficient to do it like this?

Any ideas?

Thanks

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-05 : 09:47:41

Where A.title = @RequiredTitle
AND (A.date = @RequiredDate or LEN(@RequiredDate)=0)

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-01-05 : 10:02:13

Thanks, but surely that will not work? If @RequiredDate has not been passed it would be null so I don't understand what 'AND (A.date = @RequiredDate or LEN(@RequiredDate)=0)' would achieve?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-05 : 10:06:49

>>'AND (A.date = @RequiredDate or LEN(@RequiredDate)=0)

This condition becomes True if @RequiredDate is valid date or it is null so as True and True becomes True and will work correctly regardless of the values of @RequiredDate

Madhivanan

Failing to plan is Planning to fail

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-01-05 : 10:13:51

Ah I see now that does make sense. But what of non date fields?

For example what if the where clause is:

WHERE A.group = 'audio'

But then if the passed parameter is 'video' I would then want the clause to read:

WHERE A.group = 'audio' and A.group = 'video'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-05 : 10:29:55

Search for where in @MYCSV in this topic
http://sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-01-05 : 11:46:17

"But what of non date fields?"

We use a parameter value of NULL in our "general purpose query Sprocs" to indicate "Don't care"

DECLARE @MyParameter varchar(100)

SET @MyParameter = 'audio' -- Actually pass this into the SProc, or pass in / leave as NULL

SELECT *
FROM MyTable
WHERE (@MyParameter IS NULL OR A.group = @MyParameter)

blindman was using - LEN(@MyParameter)=0 - which comes to the same thing

"But then if the passed parameter is 'video' I would then want the clause to read:

WHERE A.group = 'audio' and A.group = 'video'"

Hopefully you haven't got any columns which can actually both be "audio" and "video"!

If you want to match any of a set of multiple values to a specific column look at Madhi's link

If you were actually implying two columns then the approach above can be extended


SELECT *
FROM MyTable
WHERE     (@MyParameter1 IS NULL OR MyColumn1 = @MyParameter1)
      AND (@MyParameter2 IS NULL OR MyColumn2 = @MyParameter2)
      ....

(I believe that there is some evidence that putting the IS NULL test on the left creates a more optimal query plan that runs more quickly - but don't quote me on that!)

Kristen

jshepler
Yak Posting Veteran

60 Posts

Posted - 2006-01-05 : 11:52:25

[CODE]
CREATE PROC sp_FooBar
@Foo int = null
, @Bar datetime = null
AS

SELECT Foo, Bar
FROM SomeTable
WHERE Foo = ISNULL(@Foo, Foo)
AND Bar = ISNULL(@Bar, Bar)
[/CODE]

/jeff

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-01-06 : 03:40:22

Thanks chaps. I think I will stick with the if else approach as from what I have read so far it seems the simplest solution.

Yes we have a column which has multiple itdentifiers in it audio, video, cd, supp, main etc. So as a default I would want for example all audio, but should the user choose to they could include any of the other to be included along with audio.

Thanks.

Kristen
Test

22859 Posts

Posted - 2006-01-06 : 04:58:01

"WHERE Foo = ISNULL(@Foo, Foo) ..."

Beware this will miss rows where the column "Foo" itself contains NULLs and the caller didn't care about the value of "Foo" [assuming no freaky

ANSI settings are invoked]

Kristen

Kristen
Test

22859 Posts

Posted - 2006-01-06 : 05:02:14

"Yes we have a column which has multiple itdentifiers in it audio, video, cd, supp, main etc"

You mean the column contains a value along the lines of "Audio,Video" ?

That'll raise some hackles amongst the pedantry! (suggesting you should split them out into a child table)

Provided they are very consistently formatted you could match them with something like:


WHERE     ',' + A.group + ',' LIKE ',audio,'
      AND ',' + A.group + ',' LIKE ',video,'

If there were in a separate child table you could pass a list of ones-to-match to an Sproc, use a CSV SPLITER function to split that list into a temporary table, and then just JOIN that temp-table to the child-table and Voila!

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-06 : 05:24:49

>>WHERE Foo = ISNULL(@Foo, Foo)

As Kris said, will fail when Where Null=Null


Declare @test1 varchar(10)
Declare @test2 varchar(10)

set @test1='test'

Select * from 
(
	select 'test' as test union all
	select 'add' as test union all
	select 'more' as test union all
	select 'examples' as test union all
	select Null as test 
) T where test=Isnull(@test1,test)

Select * from 
(
	select 'test' as test union all
	select 'add' as test union all
	select 'more' as test union all
	select 'examples' as test union all
	select Null as test 
) T where test=Isnull(@test2,test)