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 |
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 flagSELECT col1,col2,col3,col4FROM table1INNER JOIN table2 ON table2.col1 = table1.col1LEFT JOIN table3 ON table3.col2 = table1.col2WHERE condition1AND ( @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 flagIF @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 condition1ELSE 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" |
|
|
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.aspxAs 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 datetimeset @minDate = case when @includeExpiredRecords then '1900-01-01' else getdate() endand then your where clause is simply:where col4 >= @minDateThat 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.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
|
|
|
|