Dynamic where clause

Question

Post reply

Dynamic where clause

UnionAll

SSCrazy

Points: 2102
More actions
December 1, 2009 at 2:56 pm

#140427

Hi,

I have a situation where i have to execute either of the

below two select's based on a parameter.

If @VarName = 'Red', first query has to be executed else second query.

--Declare @VarName varchar(40)

select col1,col2

from Table

where col3 = 'Red'

select col1,col2

from Table

where col3 <> 'Red'

Any thoughts on this please.

Thanks,

Viewing 12 posts - 1 through 11 (of 11 total)

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

SQLRNNR SSC Guru Points: 281324 More actions · Answer 1

Try this

declare @Color varchar(10)



Set @Color = 'yellow'

;

with redcte as (select 'red' as one,'red' as two,'red' as three from sys.tables

			union all

			select 'yellow' as one,'yellow' as two,'yellow' as three from sys.tables)

select one,two from redcte

where three like (case @Color when 'red' then 'red' else @Color end)

I used the CTE in order to create a result set. Just substitute your table for that of the cte

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281324 More actions · Answer 2

An alternative would be to use dynamic sql to create your statements and then execute the statement that is necessary.

Another alternative would be to use an if/else statement.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 3

Dynamic Query will be helpful...



Declare @vsql varchar(1000)

Declare @VarName varchar(1000)

Set @VarName = 'RED'

Set @vsql = 'select col1,col2 from [Table]

where col3 ' +  case when @VarName = 'RED' then ' = ' else ' <> ' end + '''RED'''

Exec (@vSQL)

Atif Sheikh

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

UnionAll SSCrazy Points: 2102 More actions · Answer 4

UnionAll

SSCrazy

Points: 2102

December 2, 2009 at 12:26 pm

#1086708

Thanks All,

i used If/Else to do this...

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 5

BaldingLoopMan

SSCertifiable

Points: 6378

December 3, 2009 at 12:42 pm

#1087328

this will work as well

select col1,col2

from Table

where col3 = case when @VarName = 'red' then @VarName else col3 end

and col3 <> case when @VarName = 'red' then '-1' else 'red' end

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

TheSQLGuru

SSC Guru

Points: 134017

December 4, 2009 at 12:19 pm

#1087958

UnionAll (12/2/2009)

Thanks All,

i used If/Else to do this...

In this case that is by far the best solution: simple, maintainable, and most important gets you the optimal plan for either condition of input.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Dave Mason SSCrazy Points: 2741 More actions · Answer 7

[font="Arial"]I prefer to avoid the IF/ELSE scenario for queries. Eventually, you'll have to "fix it" in two places. Plus, you open up the possibility of two different signatures (selecting different columns in the two queries), which could be problematic.

I'd try to keep it down to one SELECT statement. Here's another option[/font]:

Declare @VarName varchar(40)

select col1,col2

from Table

where (@VarName = 'Red' AND col3 = 'Red')

OR (@VarName <> 'Red' AND col3 <> 'Red')

Dave MasonSeminole County, FL

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

Dave Mason (12/4/2009)

[font="Arial"]I prefer to avoid the IF/ELSE scenario for queries. Eventually, you'll have to "fix it" in two places. Plus, you open up the possibility of two different signatures (selecting different columns in the two queries), which could be problematic.

I'd try to keep it down to one SELECT statement. Here's another option[/font]:

Declare @VarName varchar(40)

select col1,col2

from Table

where (@VarName = 'Red' AND col3 = 'Red')

OR (@VarName <> 'Red' AND col3 <> 'Red')

That is actually the hands-down worst thing you can do. GUARANTEED to get you the WRONG plan for one or the other of the inputs, and by wrong I mean horribly inefficient. Never, ever do that type of query.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

Dave Mason (12/4/2009)

[font="Arial"]I prefer to avoid the IF/ELSE scenario for queries. Eventually, you'll have to "fix it" in two places. Plus, you open up the possibility of two different signatures (selecting different columns in the two queries), which could be problematic.

Often multiple queries, or even multiple procedures, are the best option from a performance point of view. Yes, it's more maintenance, but if the if the single select statement takes several times the duration of the separate ones, it's not a good trade-off

See http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ and, to a lesser extent, http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 10

i personally think this will perform the best. Simply because it is or-less. Or's hurt. Sure it may be a little hard to wrap your mind around which will hurt maintainability however it is still short and to the point.

select col1,col2

from Table

where col3 = case when @VarName = 'red' then @VarName else col3 end

and col3 <> case when @VarName = 'red' then '-1' else 'red' end

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

BaldingLoopMan (12/15/2009)

i personally think this will perform the best. Simply because it is or-less. Or's hurt. Sure it may be a little hard to wrap your mind around which will hurt maintainability however it is still short and to the point.

select col1,col2

from Table

where col3 = case when @VarName = 'red' then @VarName else col3 end

and col3 <> case when @VarName = 'red' then '-1' else 'red' end

1) how would that work if @varname contained NULL?

2) Did you actually TRY your query method? Here is a demonstration of it being just as bad as ORs



use AdventureWorks

dbcc freeproccache --careful using this on production system!

declare @SalesPersonID int

SET @SalesPersonID = 288 --16 rows, should do index seek and bookmark lookup for most efficient plan

select *

from Sales.SalesOrderHeader

where SalesPersonID = case when @SalesPersonID = 288 then @SalesPersonID ELSE SalesPersonID END

AND SalesPersonID <> case when @SalesPersonID = 288 then -999999 ELSE 288 END

--this query does a full table scan with 2 scalar computes, with an estimated row count of 2831.85 after the filter, 16 rows output

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service