How to write Conditional Where Clause

Question

How to write Conditional Where Clause

descentflower

Ten Centuries

Points: 1227
More actions
June 15, 2009 at 6:02 am

#131122

Hi,
I have a simple query
select name from tableInfo
i am passing two parameters to the stored procedure
@x and @y
if @x!=0 and x is not null i want to add that in where clause for exp.
select name from tableInfo where x=@x
Similarly for @y
select name from tableInfo where x=@x and y=@y
I had use dynamic query earlier but i want to avoid it.

Viewing 13 posts - 1 through 13 (of 13 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

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

dilip_baboo SSC Enthusiast Points: 195 More actions · Answer 2

try along these lines .....

select * from Tname

where (Tcol1 = @X OR Tcol1 is null)

AND (Tcol2 = @Y OR Tcol2 is null)

---- OR -----

select * from Tname

where Tcol1 = case when @X is null then null

when @X = 0 then null

else @X end

and

Tcol2 = case when @Y is null then null

when @Y = 0 then null

else @Y end

descentflower Ten Centuries Points: 1227 More actions · Answer 3

Hi dilip,

select * from Tname

where Tcol1 = case when @X is null then null

when @X = 0 then null

else @X end

and

Tcol2 = case when @Y is null then null

when @Y = 0 then null

else @Y end

I tried this , but when i pass @x=0 it doesnt works. The Case makes it Tcol1=null.

Pandian S SSChasing Mays Points: 625 More actions · Answer 4

[font="Verdana"]Hi

SELECT [NAME] FROM TABLEINFO(NOLOCK)

WHEREX = CASE WHEN @X 0 AND @X IS NOT NULL THEN @X ELSE X END

ANDY = CASE WHEN @Y 0 AND @Y IS NOT NULL THEN @Y ELSE Y END

Regards[/font]

descentflower Ten Centuries Points: 1227 More actions · Answer 5

descentflower

Ten Centuries

Points: 1227

June 16, 2009 at 11:35 pm

#1010642

:w00t: HI Thank you it worked !! :w00t:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Just a couple things with that.

It's likely to perform badly. Take a look at the blog post I referenced earlier. Also, before you use nolock, read this: http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

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

descentflower Ten Centuries Points: 1227 More actions · Answer 7

GilaMonster (6/17/2009)
Just a couple things with that.
It's likely to perform badly. Take a look at the blog post I referenced earlier. Also, before you use nolock, read this: http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

Hi Gila,

As i am a newbie i donno so,

What bad effect will the query with 'case in where clause' bring ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Poor performance. Generally that kind of query does not use indexes effectively and often does table scans. On small tables that aren't used too often that won't be a major issue. On larger tables or busy systems it is.

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

Pyay Nyein Mr or Mrs. 500 Points: 575 More actions · Answer 9

Hi,

Half of the code is already in your question..change the and/or in the where clause according to ur requirement

SELECT[name]

FROMtableInfo

WHERE(@x 0 and @x is not null and x = @x)

and/or(@y 0 and @y is not null and y = @y)

kdsdata Valued Member Points: 65 More actions · Answer 10

Thank you Pyay Nyein.

That is one of the most useful answers. Makes the whole query look way simpler than CASE statements in the WHERE clause.

j-1064772 SSCertifiable Points: 5316 More actions · Answer 11

GilaMonster's warning about poor performance and the link mentioned rates as a most useful answer.

Jeff Moden SSC Guru Points: 1004601 More actions · Answer 12

For all those that have posted solutions, you seriously need to read Gail's "Catch All Query" article. Please refer to her first post on this thread.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

How to write Conditional Where Clause

Cookies on SQLServerCentral