June 15, 2009 at 6:02 am
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.
June 15, 2009 at 6:05 am
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
June 16, 2009 at 10:47 am
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
June 16, 2009 at 11:10 pm
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.
June 16, 2009 at 11:26 pm
[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]
June 16, 2009 at 11:35 pm
:w00t: HI Thank you it worked !! :w00t:
June 17, 2009 at 1:08 am
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
June 17, 2009 at 1:29 am
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 ?
June 17, 2009 at 1:33 am
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
June 17, 2009 at 9:17 am
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)
March 7, 2015 at 10:39 pm
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.
March 9, 2015 at 8:31 am
GilaMonster's warning about poor performance and the link mentioned rates as a most useful answer.
March 9, 2015 at 12:07 pm
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
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy