returning values when null parameter selected

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 2005 Forums

Transact-SQL (2005)

returning values when null parameter selected

Author

Topic

smt
Starting Member

1 Post

Posted - 2008-04-03 : 09:14:22

i have a stored procedure that has parameters. this dataset is used in a crystal report. the parameters are in the sp and will return records if a value is selected. i would like to return records if one parameter or all parameters are selected.
there are 3 parameters, date range, receipt, po #
if i select a distinct value and leave the others null, or 2 values 1 null etc, i would like to return the records, i am having trouble with the syntax. thank you

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-03 : 09:17:57

Something along these lines?

Select *
from Table
Where
(@param1 is null or col1 = @param1) and
(@param2 is null or col2 = @param2) and
(@param3 is null or col3 = @param3)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources