Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Dynamic SQL - which would you use? Expand / Collapse
Author
Message
Posted Thursday, January 3, 2013 8:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 3:32 AM
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.



Post #1402401
Posted Thursday, January 3, 2013 8:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:17 PM
Points: 42,450, Visits: 35,505
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 2008, MVP
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

Post #1402405
Posted Thursday, January 3, 2013 8:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 3:32 AM
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



Post #1402407
Posted Thursday, January 3, 2013 8:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 13,083, Visits: 11,918
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)
Post #1402417
Posted Friday, January 4, 2013 4:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 3:32 AM
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



Post #1402807
Posted Friday, January 4, 2013 7:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:21 PM
Points: 1,210, Visits: 2,512
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
Post #1402866
Posted Friday, January 4, 2013 7:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:17 PM
Points: 42,450, Visits: 35,505
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 2008, MVP
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

Post #1402887
Posted Friday, January 4, 2013 10:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
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?"


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1403006
Posted Friday, January 4, 2013 11:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:40 PM
Points: 36,751, Visits: 31,200
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1403029
Posted Friday, January 4, 2013 12:56 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 1,967, Visits: 2,905
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1403078
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse