SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Search Query


Search Query

Author
Message
shatrughna
shatrughna
SSC-Addicted
SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)

Group: General Forum Members
Points: 439 Visits: 1587
Hi,
I have to write a query for searching records.
I have four input fields.
If USER is entering value in one
input field data to be serached on that specific single field.
If more than one entry then all parameters should be ANDed.
I don't want to use if A='' Then Do this

Please suggest me any good idea.

Shatrughna
Nabha
Nabha
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3545 Visits: 1815
'Catch all' ,follow the link

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

---------------------------------------------------------------------------------
Elliott Whitlow
Elliott Whitlow
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49116 Visits: 5314
You could try this:
SELECT * 
FROM dbo.SomeTable
WHERE ( @Field1 IS NULL OR Field1 = @Field1 )
AND ( @Field2 IS NULL OR Field2 = @Field2 )
AND ( @Field3 IS NULL OR Field3 = @Field3 )
AND ( @Field4 IS NULL OR Field4 = @Field4 )


Provided that the search fields are NULL when unused and non-NULL when used.

CEWII
shatrughna
shatrughna
SSC-Addicted
SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)

Group: General Forum Members
Points: 439 Visits: 1587
Thanks.
Appreciated.

Shatrughna
GilaMonster
GilaMonster
SSC Guru
SSC Guru (464K reputation)SSC Guru (464K reputation)SSC Guru (464K reputation)SSC Guru (464K reputation)SSC Guru (464K reputation)SSC Guru (464K reputation)SSC Guru (464K reputation)SSC Guru (464K reputation)

Group: General Forum Members
Points: 464348 Visits: 47315
Elliott W (11/17/2009)
You could try this:
SELECT * 
FROM dbo.SomeTable
WHERE ( @Field1 IS NULL OR Field1 = @Field1 )
AND ( @Field2 IS NULL OR Field2 = @Field2 )
AND ( @Field3 IS NULL OR Field3 = @Field3 )
AND ( @Field4 IS NULL OR Field4 = @Field4 )


Provided that the search fields are NULL when unused and non-NULL when used.


You could, providing that performance is not a major concern. Take a look at the link that Nabha posted.

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


Elliott Whitlow
Elliott Whitlow
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49116 Visits: 5314
G,
I got the parameter sniffing vibe off that article.. I have always had really good performance off these kinds of queries because I have avoided the causes of that. As far as performance the where clause only gets evaluated once. The optimizer knows that if the variable is NULL that is already has a positive result so it doesn't even have to use that in the plan..

I tend to think this is better than dynamic SQL.. But I may do some additional testing..

CEWII
GilaMonster
GilaMonster
SSC Guru
SSC Guru (464K reputation)SSC Guru (464K reputation)SSC Guru (464K reputation)SSC Guru (464K reputation)SSC Guru (464K reputation)SSC Guru (464K reputation)SSC Guru (464K reputation)SSC Guru (464K reputation)

Group: General Forum Members
Points: 464348 Visits: 47315
Elliott W (11/17/2009)
I got the parameter sniffing vibe off that article..


It's not so much parameter sniffing. It's that there's no single optimal execution plan for that kind of query and, because the optimiser has to pick a safe plan, it generally picks a sub-optimal one.

The optimizer knows that if the variable is NULL that is already has a positive result so it doesn't even have to use that in the plan..

Not really. Optimiser doesn't deal well with multiple '@variable is null OR' constructs. Plus it can't (except in 2008 when OPTION RECOMPILE is specified) not use a particular branch in the plan when a parameter is null, because that plan will be cached for reuse and next time the query runs the parameter may not be null. If it made that assumption, the plan would result in incorrect results sometimes. That's not allowed, the plan must always be safe for reuse.

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search