SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 why would/wouldn't I want to do this?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mushu
Starting Member

4 Posts
Posted - 04/16/2008 :  17:44:48  Show Profile  Reply with Quote
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

Sweden
29156 Posts
Posted - 04/16/2008 :  18:45:05  Show Profile  Visit SwePeso's Homepage  Reply with Quote 
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()


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

jsmith8858
Dr. Cross Join

USA
7423 Posts
Posted - 04/16/2008 :  19:28:18  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Edited by - jsmith8858 on 04/16/2008 19:29:49
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000