Shortcuts on and/or logic with different variables

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)

Shortcuts on and/or logic with different variables

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2008-03-06 : 10:13:09

Hi,

I have a relatively common scenario. There is a stored procedure which takes two input parameters which are used as clauses in the query, e.g:


select * from myTable where (field1 = @param1 or field2 = @param2)

Now, the behaviour I'm ideally looking for is for the SP to implement different logic depending on what parameters have been supplied. If either one of the params has a value of zero then I want to run the query on the other param only, or if both params have values greater than zero, then I want to run the query using both parameters e.g.:


pseudocode:
If @param1 = 0 then
    select * from myTable where  field2 = @param2
else if @param2 = 0 then
    select * from myTable where  field1 = @param1
else if param2 <> 0 and param1 <> 0 then
    select * from myTable where  field1 = @param1 and field2 = @param2

Looks straightforward enough. In reality of course the actual query is much more complicated than that, and it's a mess to write it out three times just to do a bit of if/else logic.

There are two obvious solutions to this: either do the if/else in code and call different SPs, or make the query an SP on its own which is called by my original SP with different logic. For various reasons I'd rather not do either of these things.

This is such a common scenario that I'm wondering whether or not there's some easier, cleaner solution that I've missed, or don't know about. Is there?

Cheers,
Matt

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-06 : 10:38:25

[code]
select * from myTable
where (field1 = @param1 or @param1 = 0) and
(field2 = @param2 or @param2 = 0)
[/code]

however due to query plan caching it is recommended that you call a new sproc for each search type.
this depends on how large your table is.
but lately i'm more and more leaning towards dynamic sql for these kinds of searches.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-06 : 10:58:33

I also favor dynamic SQL for things like this, because you usually get better query plans. Search type queries tend to be low volume, so having to recompile the execution plan for each search is not a big problem.

If you have a situation where it is higher volume and having it recompile the execution plan for each search is a problem, you can use an in-between approach where you have queries for the most commonly used parameter combinations, and use dynamic SQL for the rest. If the usage pattern follows the typical 80/20 distribution, this should work OK without having to code a huge number of combinations.

If @param1<> 0 and @param2 <> 0 and @param3 <> 0 and @param4 <> 0   goto Query_One
If @param1<> 0 and @param2 <> 0 and @param3 <> 0 and @param4 = 0    goto Query_Two
If @param1<> 0 and @param2 <> 0 and @param3 =  0 and @param4 <> 0   goto Query_Three

Goto Query_Dynamic

Query_One:
… query 1
Return

Query_Two:
… query 2
Return

Query_Three:
… query 3
Return

Query_Dynamic:
… query with dynamic SQL
Return

CODO ERGO SUM

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-06 : 11:13:34

no no no goto please!!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-06 : 11:53:47

There are, of course, other flow of control methods available to those who cannot handle a goto.

CODO ERGO SUM

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-06 : 12:04:36

"you want goto? you can't handle the goto!"

boy that quote can be used for so many things...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources