empty parameters into a Stored Procedure

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

General SQL Server Forums

New to SQL Server Programming

empty parameters into a Stored Procedure

Author

Topic

madtowner11
Starting Member

1 Post

Posted - 2006-09-13 : 12:31:10

Greetings! This is my first ever post here, so please be gentle.

I have a stored procedure that will be accepting many parameters (around 10). Any number of them can be empty (empty with a length of zero, but probably not Null per se).

How do I do a Select... Where... where if the parameter is empty it ignores it in the 'Where' but if the parameter had anything in it, it becomes part of the filter?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-13 : 12:41:11

Switch your application to send in NULL values for the paramters that will be empty, then use COALESCE:

http://www.sqlteam.com/item.asp?ItemID=2077

Tara Kizer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-13 : 13:18:58

Or

SELECT * FROM MyTable
WHERE (ISNULL(@Param1, '') = '' OR Column1 = @Param1)
AND (ISNULL(@Param2, '') = '' OR Column2 = @Param2)
AND (ISNULL(@Param3, '') = '' OR Column3 = @Param3)
AND (ISNULL(@Param4, '') = '' OR Column4 = @Param4)
AND (ISNULL(@Param5, '') = '' OR Column5 = @Param5)
AND (ISNULL(@Param6, '') = '' OR Column6 = @Param6)
AND (ISNULL(@Param7, '') = '' OR Column7 = @Param7)
AND (ISNULL(@Param8, '') = '' OR Column8 = @Param8)
AND (ISNULL(@Param9, '') = '' OR Column9 = @Param9)
AND (ISNULL(@Param10, '') = '' OR Column10 = @Param10)

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-14 : 05:52:06

quote:
Originally posted by Peso

Or

SELECT * FROM MyTable
WHERE (ISNULL(@Param1, '') = '' OR Column1 = @Param1)
AND (ISNULL(@Param2, '') = '' OR Column2 = @Param2)
AND (ISNULL(@Param3, '') = '' OR Column3 = @Param3)
AND (ISNULL(@Param4, '') = '' OR Column4 = @Param4)
AND (ISNULL(@Param5, '') = '' OR Column5 = @Param5)
AND (ISNULL(@Param6, '') = '' OR Column6 = @Param6)
AND (ISNULL(@Param7, '') = '' OR Column7 = @Param7)
AND (ISNULL(@Param8, '') = '' OR Column8 = @Param8)
AND (ISNULL(@Param9, '') = '' OR Column9 = @Param9)
AND (ISNULL(@Param10, '') = '' OR Column10 = @Param10)

Peter Larsson
Helsingborg, Sweden

or this...

SELECT * FROM MyTable
WHERE (@Param1 IS NULL OR Column1 = @Param1)
AND (@Param2 Is NULL OR Column2 = @Param2)
....

to avoid TABLE SCAN due to non-SARGable condition

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-14 : 06:17:31

Then you don't catch this

quote:
Any number of them can be empty (empty with a length of zero, but probably not Null per se).

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-14 : 07:27:31

quote:
Originally posted by Peso

Then you don't catch this
quote:
Any number of them can be empty (empty with a length of zero, but probably not Null per se).

Peter Larsson
Helsingborg, Sweden

oh, right !!

Thanks peter!

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources