<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Development
»
dynamic WHERE clause
13 posts, Page 1 of 2
1
2
»»
dynamic WHERE clause
Rate Topic
Display Mode
Topic Options
Author
Message
DenisT
DenisT
Posted Friday, November 09, 2012 1:16 PM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 3:23 PM
Points: 24,
Visits: 187
Hello,
I have a query that have a dynamic WHERE clause. The query's WHERE clause is built based on the web page that a user is on. I was wondering, what is the best approach to deal with this kind of situation in SQL Server 2005? Everything is the same in the query but the WHERE clause.
Should I have 1 stored procedure with IFs? Should I have a stored procedure for each WHERE clause? Something else?
WHERE (B.BusinessStatus='A')
-----------------------------------------------------------------------------------------
-- WHERE clause for MG Industry
-----------------------------------------------------------------------------------------
AND ( (B.MarketGuideIndustryNumber=727)
OR (B.BusinessId=13425908))
-----------------------------------------------------------------------------------------
-- WHERE clause for SIC
-----------------------------------------------------------------------------------------
--AND (B.BusinessId IN ((SELECT DISTINCT BusinessID FROM BusinessSICCodes WITH (NOLOCK) WHERE SICCode='7374')
-- UNION
-- (SELECT BusinessId=244)))
-----------------------------------------------------------------------------------------
-- WHERE clause for NAICS
-----------------------------------------------------------------------------------------
--AND (B.BusinessId IN ((SELECT DISTINCT BusinessID FROM BusinessNAICSCodes WITH (NOLOCK) WHERE NAICSCode='541519')
-- UNION
-- (SELECT BusinessId=244)))
-----------------------------------------------------------------------------------------
-- WHERE clause for User's Portfolio
-----------------------------------------------------------------------------------------
--AND (B.BusinessId IN ((SELECT DISTINCT BusinessId FROM UserBusinesses WITH (NOLOCK) WHERE UserId=19601)
-- UNION
-- (SELECT BusinessId=244)))
-----------------------------------------------------------------------------------------
-- WHERE clause for a Personal Folder
-----------------------------------------------------------------------------------------
--AND (B.BusinessId IN ((SELECT DISTINCT BusinessId
-- FROM FolderBusinesses F WITH (NOLOCK)
-- INNER JOIN PortfolioFolders P WITH (NOLOCK) ON F.FolderId=P.FolderId
-- WHERE P.UserId=19601
-- AND P.FolderId='3')
-- UNION
-- (SELECT BusinessId=244)))
Thank you in advance!
Denis
Post #1383217
Luis Cazares
Luis Cazares
Posted Friday, November 09, 2012 1:36 PM
SSC Eights!
Group: General Forum Members
Last Login: 2 days ago @ 3:26 PM
Points: 958,
Visits: 1,917
Take a look at this article by Gail Shawn on "Catch all queries"
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Luis C.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help
Post #1383223
Sean Lange
Sean Lange
Posted Friday, November 09, 2012 1:44 PM
SSCrazy Eights
Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 8,547,
Visits: 8,204
I second reading the article by Gail. Also, why all the NOLOCK hints? Are you aware of the possible risks using that?
_______________________________________________________________
Need help? Help us help you.
Read the article at
http://www.sqlservercentral.com/articles/Best+Practices/61537/
for best practices on asking questions.
Need to split a string? Try Jeff Moden's
splitter
.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1383226
Bill Talada
Bill Talada
Posted Friday, November 09, 2012 1:52 PM
Valued Member
Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:31 AM
Points: 65,
Visits: 443
There is no BEST way to do anything since aspects of each situation will vary.
I like your idea of IFs in a stored procedure since each option may be optimized separately.
I use sp_execute when any/all parameters may be used in a dynamic WHERE clause.
And remember that SQL is also cached so a stored procedure won't get you better performance.
BTW, no need to do a SELECT DISTINCT when using an IN clause. It is implicitly done.
Post #1383229
DenisT
DenisT
Posted Monday, November 12, 2012 6:17 AM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 3:23 PM
Points: 24,
Visits: 187
Luis, thank you for your reply! Gail ROCKS :) I've been reading her blog for a while now! I think I have an idea now how to deal with this.
Denis
Post #1383654
DenisT
DenisT
Posted Monday, November 12, 2012 6:19 AM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 3:23 PM
Points: 24,
Visits: 187
Sean,
Yes, I am aware about the possibility of dirty reads while using the NOLOCK hint. I really don't care about them in this case. I needed avoid any locking.
Denis
Post #1383655
DenisT
DenisT
Posted Monday, November 12, 2012 6:24 AM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 3:23 PM
Points: 24,
Visits: 187
Bill,
Thank you! As always -- it depends and there is no best way :) Didn't know about the SELECT DISTINCT, do you know where I could read about this? I'd like to understand it!
Appreciate your input!
Denis
Post #1383659
Sean Lange
Sean Lange
Posted Monday, November 12, 2012 7:11 AM
SSCrazy Eights
Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 8,547,
Visits: 8,204
DenisT (11/12/2012)
Sean,
Yes, I am aware about the possibility of dirty reads while using the NOLOCK hint. I really don't care about them in this case. I needed avoid any locking.
Denis
It can be far worse than just uncommitted data. You can get duplicates or missing data. If possible, using isolation levels would be far better. I am at home today so I don't have any links. It was an eye opener for me some of the major pitfalls of that hint. If nobody beats me to it I will post some links tomorrow.
_______________________________________________________________
Need help? Help us help you.
Read the article at
http://www.sqlservercentral.com/articles/Best+Practices/61537/
for best practices on asking questions.
Need to split a string? Try Jeff Moden's
splitter
.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1383683
DenisT
DenisT
Posted Monday, November 12, 2012 7:47 AM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 3:23 PM
Points: 24,
Visits: 187
Sean,
Didn't know that either! That would be great! I really appreciate it.
Denis
Post #1383705
Sean Lange
Sean Lange
Posted Monday, November 12, 2012 9:39 AM
SSCrazy Eights
Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 8,547,
Visits: 8,204
Here is one of the articles that explains the deeper issues with NOLOCK.
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/
_______________________________________________________________
Need help? Help us help you.
Read the article at
http://www.sqlservercentral.com/articles/Best+Practices/61537/
for best practices on asking questions.
Need to split a string? Try Jeff Moden's
splitter
.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1383746
« Prev Topic
|
Next Topic »
13 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
