Dynamic Optional Parameters - 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

General SQL Server Forums

New to SQL Server Programming

Dynamic Optional Parameters

Author

Topic

sw0rdf1sh7
Starting Member

15 Posts

Posted - 2008-02-13 : 16:08:51

Good day,

I have an issue on constructing dynamic WHERE conditions that use OPTIONAL parameters.

SP_SOMETHING (
1) @DateFrom datetime,
@DateTo datetime,
2) @Param1 char(8),
3) @Param2 char(3),
4) @Param3 tinyint
)

I would like to use a where clause that can make use of any combination of the 4 parameters (the two dates should be together)

1 2 3 4
/ x x x
x / x x
x x / x where x = not supplied
/ = supplied a value

(and so the list continues)
Can anybody assist me or give me insights on how to go about this complicated WHERE construct without listing all the probable combinations of the supplied parameters in series of IF statements.

thank you

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-02-13 : 22:48:31

In the parameter list for the proc, give the params default values of NULL like this

SP_SOMETHING (
@DateFrom datetime = NULL,
@DateTo datetime = NULL,
@Param1 char(8) = NULL,
@Param2 char(3) = NULL,
@Param3 tinyint = NULL 
)

Then in your WHERE clause check for the NULL something like this

WHERE (@DateFrom IS NULL OR @DateTo IS NULL OR datecol BETWEEN @DateFrom AND @DateTo)
AND (@Param1 IS NULL OR col1 = @Param1)
AND (@Param2 IS NULL OR col2 = @Param2)
AND (@Param3 IS NULL OR col3 = @Param3)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-14 : 03:56:45

Also refer
www.sommarskog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

sw0rdf1sh7
Starting Member

15 Posts

Posted - 2008-02-14 : 09:42:39

Thanks snSQL and madhivanan. These are of great help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources