Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic SQL - which would you use?


Dynamic SQL - which would you use?

Author
Message
tony@kenny.net
tony@kenny.net
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 21
When I need to execute code depending upon the values of parameters I often implement one of 2 methods:
In this method, I'm setting a boolean column, either by ID if it is provided, or if it is not then we're setting all records that are not already set.

Method 1: I have a different query depending on the parameter


IF @ID IS NULL
BEGIN
UPDATE myTable
SET ClearFlag = 1
WHERE ClearFllg = 0
END
ELSE
BEING
UPDATE myTable
SET ClearFlag = 1
WHERE ID = @ID
END



Method 2: I include the parameter in the query


UPDATE myTable
SET ClearFlag = 1
WHERE
(@id IS NULL AND ID = @ID)
OR
(cleared = 0 AND @ID IS NULL)



For a simple query like this, either way probably works just as well. When multiple parameters need to be taking into account I consider that the first way maybe executes faster but if any changes are required then they must be made in all the 'copied' queries. The 2nd method may not execute as quickly (thought I've not really tested), it can result in some complex clauses but means there is only one query.

My question: Which way do you swing? Do you have any other ways? Any other comments welcome.



GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47234 Visits: 44373
Neither. I'd use dynamic SQL.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/


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


tony@kenny.net
tony@kenny.net
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 21
I used to use dynamic SQL but it was always so ugly and not particularly friendly when it comes to code maintenance.
I was also concerned about how well it performs and a whole new execution plan will need to be generated if so much as one character changes.

Interested to hear opinions.

Tony



Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16550 Visits: 17004
tony@kenny.net (1/3/2013)
I used to use dynamic SQL but it was always so ugly and not particularly friendly when it comes to code maintenance.
I was also concerned about how well it performs and a whole new execution plan will need to be generated if so much as one character changes.

Interested to hear opinions.

Tony


Read the two articles Gail wrote and posted. She explains in detail about both of your types of queries and why dynamic sql will outperform either of the methods you posted. :-)

_______________________________________________________________

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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
tony@kenny.net
tony@kenny.net
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 21
Hi,
Sorry, I misunderstood the links and thought they were just part of the signature so didn't give them any attention.

I've read the articles, thank you so much!
Looks like I was doing things right many many years ago with MS SQL 6 and my decision to change a few years ago was actually a bad move (at least for certain scenarios)!

Thank you for the great articles and for opening my eyes. I'm glad I got curious and asked!

best regards,
Tony



wolfkillj
wolfkillj
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1104 Visits: 2582
tony@kenny.net (1/3/2013)
I used to use dynamic SQL but it was always so ugly and not particularly friendly when it comes to code maintenance.
I was also concerned about how well it performs and a whole new execution plan will need to be generated if so much as one character changes.

Interested to hear opinions.

Tony


IIRC, SQL Server can auto-parameterize a dynamic T-SQL query and cache the execution plan like any other. When future calls generate and execute the same dynamic T-SQL query, SQL Server will use the cached execution plan. So you could end up with separate plans for all possible variants of the dynamic T-SQL query, but as long as the execution plans remain in the plan cache, SQL Server will reuse them each time the same variants are executed.

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47234 Visits: 44373
wolfkillj (1/4/2013)
IIRC, SQL Server can auto-parameterize a dynamic T-SQL query and cache the execution plan like any other.


It'll always cache the plan, parameterised or not. Only very simple queries (no joins) qualify for auto-parameterisation. That said, the method in my blog posts uses a parameterised piece of dynamic SQL, meaning that queries of the same form with different parameter values will reuse plans.


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


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45042 Visits: 39896
CELKO (1/4/2013)
When I need to execute code depending upon the values of parameters I often implement one of 2 methods:


I see that you also confuse rows and records. Since SQL is declarative, the use of the T-SQL's procedural 4GL control flow should be very rare.

Have you ever had a basic Software Engineering course? Look up the terms “coupling” and “cohesion” in your unread text book. This is not SQL; this is much more fundamental. A module of code should have one and only one entry point, one and only one exit point, and perform one and only one task. The worst possible way to program is with bit flags that control the “Automobiles, Squids and Lady Gaga” module. The best way is to have highly coherent modules that are loosely coupled. Think of a math function.

Yourdon? DeMarco? Constantine? If this was a woodworking newsgroup and someone posted "What is the best kind of rocks to pound screws into fine furniture?" are you really helping them when you say "Granite! Use big hunks of granite!" I am the guy who replies with "Your question is bad. Don't you know about screwdrivers?"


Jeez, Joe... get over it.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3943 Visits: 6674
CELKO (1/4/2013)
When I need to execute code depending upon the values of parameters I often implement one of 2 methods:


I see that you also confuse rows and records. Since SQL is declarative, the use of the T-SQL's procedural 4GL control flow should be very rare.

Have you ever had a basic Software Engineering course? Look up the terms “coupling” and “cohesion” in your unread text book. This is not SQL; this is much more fundamental. A module of code should have one and only one entry point, one and only one exit point, and perform one and only one task. The worst possible way to program is with bit flags that control the “Automobiles, Squids and Lady Gaga” module. The best way is to have highly coherent modules that are loosely coupled. Think of a math function.

Yourdon? DeMarco? Constantine? If this was a woodworking newsgroup and someone posted "What is the best kind of rocks to pound screws into fine furniture?" are you really helping them when you say "Granite! Use big hunks of granite!" I am the guy who replies with "Your question is bad. Don't you know about screwdrivers?"



Devoid as usual of any actual recommendation.

What specifically should requestor do then?

What specifically then should one do when a table has, say, 50 columns that can be arbitrarily filtered by users in self-directed queries, and you have to provide the supporting code?

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
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