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
 why would/wouldn't I want to do this?

Author  Topic 

mushu
Starting Member

4 Posts

Posted - 2008-04-16 : 17:44:48
I have a webpage that calls a stored proc and passes several parameters to it. The query takes those values assigned to variables and in order to "test" them for the correct output, it uses them in the WHERE clause like the following example. What is bad (or good) about doing it this way? I chose this method because it seemed much easier to read/understand and the SQL engine uses short-circuit boolean evaluation so the performance impact should be minimal. Contrived pseudocode:

DECLARE @includeExpiredRecords smallint -- user indicated flag
SELECT col1,col2,col3,col4
FROM table1
INNER JOIN table2 ON table2.col1 = table1.col1
LEFT JOIN table3 ON table3.col2 = table1.col2
WHERE condition1
AND (
@includeExpiredRecords=1 -- include expired records
OR (
@includeExpiredRecords=0 AND col4 >= getdate() -- exclude expired
)
)

This will take the tables, join them, and test the two where clause parameters: the first one being condition1 and the second one being EITHER the expired flag being equal to 1 OR (the expired flag being equal to 0 AND the data being later than today's date). Note the parens.

I have a lot of parameters passed in from the webpage that I use this way. It seemed easier to me at the time to do it this way rather than build a bunch of temp tables at the beginning and then join those into the queries later on.

Please give me pros and cons of doing it this way. I bet some of you haven't seen this or ever thought to do it this way before... :-)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-16 : 18:45:05
[code]DECLARE @includeExpiredRecords smallint -- user indicated flag

IF @includeExpiredRecords = 1
SELECT col1,
col2,
col3,
col4
FROM table1
INNER JOIN table2 ON table2.col1 = table1.col1
LEFT JOIN table3 ON table3.col2 = table1.col2
WHERE condition1
ELSE
SELECT col1,
col2,
col3,
col4
FROM table1
INNER JOIN table2 ON table2.col1 = table1.col1
LEFT JOIN table3 ON table3.col2 = table1.col2
WHERE condition1
AND col4 >= getdate()[/code]


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-04-16 : 19:28:18
mushu -- that's a pretty standard technique, it is logically sound, and it works well. SQL Server 2005 and newer seems to be much more efficient with OR's than SQL 2000 as well.

What Peso suggested is OK if you have one parameter, but with multiple parameters the number of options you have grows exponentially and it can be a nightmare to maintain.

However, take a look at this:

http://weblogs.sqlteam.com/jeffs/archive/2007/09/18/sql-conditional-where-clauses.aspx

As I talk about there, sometimes you can rewrite things a little to avoid OR's and also avoid using IF's and multiple SELECT's. In your case, for example, you can do this:

declare @MinDate datetime
set @minDate = case when @includeExpiredRecords then '1900-01-01' else getdate() end

and then your where clause is simply:

where col4 >= @minDate

That can often be a very efficient way to do things, it avoids OR's, your logic is clear, and it can be easier to debug sometimes as well. Be sure you have good indexes on your table as well.

In short, it is good to have a number of techniques in your toolbox; in the end, you should test a variety of them out in different situations to see which works best.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page
   

- Advertisement -