Case in Where clause

Question

Case in Where clause

asdf55

Newbie

Points: 9
More actions
January 18, 2002 at 9:20 am

#78103

I'd like to be able to replace the search condition of a WHERE clause based on the value of a variable. For example, in one case the search condition might be "WHERE ControlNum = '555'", in another it might be "WHERE Department = 'Sales'. Is this possible? *Note - currently using 7, about to upgrade to 2000. Thanks, TedG

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Paul Ibison SSCertifiable Points: 5303 More actions · Answer 1

This is called "lazy evaluation" - I have a script on this site which shows an example. I think it's really only of academic interest in SQL for everything but small tables or queries which will bring back most rows. The problem is that the query optimiser can't evaluate which indexes to use, and ends up doing a table scan. In my case I decided to use dynamic sql - concatenating different where clauses onto a textual sql statement depending on which criteria were entered. This can still be fast, but you lose the syntax checking so coding it can be a bit of a pain if it's long. even so I'd use this in your case. Hope this helps.

Paul Ibison

Paul.Ibison@btinternet.com

Paul Ibison
Paul.Ibison@replicationanswers.com

Leon Platt SSCommitted Points: 1921 More actions · Answer 2

If there is a limited number of columns you will use in the where clause you could build a stored procedure and pass in the columnName and value

Then you could choose which sql statement to run based on the columnName passed in.

And like paul said you could use dynamic sql

or you could build the sql statement using vbscript and use ado to execute it.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

This defeats the benefits of stored procedures. You'd be better off writing multiple stored procedures and calling the appropriate one. You can even write on main procedure that determines which is the right one to call and calls it. This will simplify things for developers, but still maintain the benefits of stored procedures.

Steve Jones

steve@dkranch.net

Antares686 SSC Guru Points: 125444 More actions · Answer 4

I have done this in the past and has worked for me.

WHERE

(CASE WHEN conditiontomeet=met THEN ControlNum ELSE 0 END) = (CASE WHEN conditiontomeet=met THEN '555' ELSE 0 END) AND

(CASE WHEN otherconditiontomeet=met THEN Deparment ELSE 0 END) = (CASE WHEN otherconditiontomeet=met THEN 'Sales' ELSE 0 END)

What happens is when the condition is met then the value of the column is compared to the value you want. Otherwise it evaluates 0=0 which is true always and those does not block data.

Deuce SSCrazy Points: 2712 More actions · Answer 5

Anteres,

Your method works, but forces a table scan. I tried it out and it's S L O W. 7 seconds vs under 1 second executing a SQL String built in code on a table with ~5 million rows. Appearantly, SQL can still figure out which index to use when executing the string.

Steve, there are other benefits to stored procedures besides a pre-defined execution plan. Sometimes you just gotta do it.

Peace

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Hem, odd. Does not do this to me. I must check further into this. as 0 always is 0 and when it compiles it should never have to check again. I will let you know if I find a specific reason or better way to get around what is happening to you.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

Deuce, your comments match up with what Paul has said earlier. And for ad-hoc queries, SQL Server 7 and 2000 will generate an execute plan, which means it'll see about using indexes if they are appropriate. If you are building these dynamic SQL strings and executing them a lot, under SQL Server 2000 there is a better chance their execution plan will get cached and reused, as well. SQL Server 2000 has better pattern recognition when it comes to parsing an ad hoc query and storing an execution plan.

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 8

Brian has a good explanation. I like stored procedures as they also promote resuse and encapsulate specific functions. I take the view that I'll build 10 sprocs to cover your nine cases rather than do dynamic sql. Just my opinion though.