conditional where clause

Question

Post reply

conditional where clause

Viewing 5 posts - 16 through 19 (of 19 total)

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

marc.corbeel Default port Points: 1484 More actions · Answer 1

Found a very simple solution to my problem...

create procedure stp_test (@only_this_record int)

as

begin

select * from test where (field1=1) and ...etc...

and (id=@only_this_record or @only_this_record=0)

end

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

marc.corbeel (12/14/2016)
Found a very simple solution to my problem...
create procedure stp_test (@only_this_record int)
as
begin
select * from test where (field1=1) and ...etc...
and (id=@only_this_record or @only_this_record=0)
end

I hope you don't need decent performance from that, because that query form is horribly prone to really bad performance.

https://www.simple-talk.com/content/article.aspx?article=2280

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

Shekhu Right there with Babe Points: 780 More actions · Answer 3

Check the below code, ISNULL function should help to resolve this. Adding Recompile will take care of changing the query plan in case the variable is NULL and you end up pulling all records from the table.

declare @Var INT

select top 10 * from tableName where ColumName= ISNULL(@Var,ColumName)OPTION (RECOMPILE)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Shekhu (12/15/2016)
Check the below code, ISNULL function should help to resolve this.
declare @Var INT
select top 10 * from tableName where ColumName= ISNULL(@Var,tableName)

Actually it won't, it's the same problem. Read this article [/url]to find out why.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

MMartin1 One Orange Chip Points: 27655 More actions · Answer 5

ChrisM@Work (12/15/2016)
Shekhu (12/15/2016)
Check the below code, ISNULL function should help to resolve this.
declare @Var INT
select top 10 * from tableName where ColumName= ISNULL(@Var,tableName)
Actually it won't, it's the same problem. Read this article [/url]to find out why.

I would have thought there would be some upside. The post you copied leaves out the option(recompile) hint at the end of the statement. From the article

with the OPTION (RECOMPILE) hint, then the optimiser knows it doesn’t have to worry about safe plans because the plan will never be reused.

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