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.
| Author |
Topic |
|
mattt
Posting Yak Master
194 Posts |
 Posted - 2008-03-28 : 11:19:55
|
Hi,I need to modify an already existing stored procedure, which takes three parameters. Currently it uses exclusively "or" logic to apply the parameters to a select statement. e.g. and (adid = @adID or (editorID = @editorID and aa.editorTypeID = 1)) What I need it to do is to apply a different "and/or" logic depending on what is supplied. If two or three parameters are supplied then it needs to use "and" logic to join them. Howver, if just one parameter is supplied then it should search on that parameter alone - which it won't do if I just join them all together using "and" by default.The work has to be done inside the T-SQL, not in the calling code. I only know two ways of doing this. Firstly to create different stored procedures and use the calling code to pick one or the other depending on supplied parameters. The other is to do if/or login inside the SP itself on the values of the parameters. Either way presents something of a maintenance nightmare. Is there any other way to do this?Cheers,Matt |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-28 : 11:23:12
|
The basic idea isWHERE (Col1 = @Param1 OR @Param1 IS NULL) AND (Col2 = @Param2 OR @Param2 IS NULL) AND (Col3 = @Param3 OR @Param3 IS NULL)
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
mattt
Posting Yak Master
194 Posts |
Posted - 2008-03-28 : 11:33:24
|
| Excellent, thanks! |
|
|
|
|
|
|
Applying and/or in Stored Procedure