Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to write Conditional Where Clause Expand / Collapse
Author
Message
Posted Monday, June 15, 2009 6:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, February 1, 2012 8:08 AM
Points: 65, Visits: 153
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.
Post #734919
Posted Monday, June 15, 2009 6:05 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #734924
Posted Tuesday, June 16, 2009 10:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 27, 2012 2:02 AM
Points: 33, Visits: 53
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

Post #735848
Posted Tuesday, June 16, 2009 11:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, February 1, 2012 8:08 AM
Points: 65, Visits: 153
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.
Post #736213
Posted Tuesday, June 16, 2009 11:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 20, 2011 6:49 AM
Points: 129, Visits: 143
Hi

SELECT [NAME] FROM TABLEINFO(NOLOCK)
WHERE X = CASE WHEN @X <>0 AND @X IS NOT NULL THEN @X ELSE X END
AND Y = CASE WHEN @Y <>0 AND @Y IS NOT NULL THEN @Y ELSE Y END

Regards
Post #736217
Posted Tuesday, June 16, 2009 11:35 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, February 1, 2012 8:08 AM
Points: 65, Visits: 153
HI Thank you it worked !!
Post #736219
Posted Wednesday, June 17, 2009 1:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
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

Post #736254
Posted Wednesday, June 17, 2009 1:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, February 1, 2012 8:08 AM
Points: 65, Visits: 153
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 ?
Post #736260
Posted Wednesday, June 17, 2009 1:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
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

Post #736262
Posted Wednesday, June 17, 2009 9:17 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 3, 2014 8:06 AM
Points: 95, Visits: 368
Hi,

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

SELECT [name]
FROM tableInfo
WHERE (@x <> 0 and @x is not null and x = @x)
and/or (@y <> 0 and @y is not null and y = @y)
Post #736690
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse