SQL Clone
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 Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90943 Visits: 45284
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
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27018 Visits: 17557
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 Modens 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
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1504 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 Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90943 Visits: 45284
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 Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90125 Visits: 41146
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8276 Visits: 7163
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)[size=2]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.[/size]
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