Best practice recommendations for writing Dynamic SQL

12

June 12, 2013 by Kenneth Fisher

Note this is not “Best Practices when USING Dynamic SQL”. These are just good habits I’ve come up with over the years to make sure that when writing my dynamic SQL it breaks less often. Note I don’t say “doesn’t break ever”, dynamic SQL is tricky stuff. The best you are likely to get is “less often”. Some people may manage 100%, I’m not one of them, but I’ll bet that if they are out there then they use every one of these recommendations.

Generally the problem with dynamic SQL revolves around the fact that, you guessed it, it’s dynamic. It changes based on the data. This means that in order to keep from having problems the statements you create need to be as robust as possible. Every single one of these recommendations are things that you can and probably should use in regular SQL, but most people frequently don’t.

Always use []s around object names.

EXEC sp_msforeachdb 'USE [?];'

I can hear someone in the back saying “I can ignore this one, all of my names work without the brackets.” Maybe true, maybe not, but I’ll almost guarantee that at some point one of your database names, object names etc will end up starting with a number, have a space or dash in it, a symbol, etc and your dynamic SQL is going to break.

Always put EXEC in front of stored procedure calls

SELECT 'EXEC sp_spaceused ['+TABLE_SCHEMA+'.'+TABLE_NAME+'];'
FROM INFORMATION_SCHEMA.TABLES

I wrote about this one time here. This one is a good habit anyway, but particularly with dynamic SQL. SQL Server only runs a stored procedure without EXEC if it’s the first thing in the batch. If you are writing dynamic SQL to say generate a group of commands that run a given stored procedure for every table in the database it would be nice if you could run the whole thing at once, not one line at a time.

Always include the Schema where appropriate

SELECT 'SELECT COUNT(1) FROM ['+TABLE_SCHEMA+'].['+TABLE_NAME+']'
FROM INFORMATION_SCHEMA.TABLES

First of all I say “where appropriate” because databases, logins etc don’t have schemas to use. Now in response to that same heckler in the back is now saying “All of my objects are in the dbo schema, I don’t need to include it.” Really? Are you sure? Someone didn’t create an object while you weren’t looking? The vendor software you just bought only uses dbo? You would be amazed how often systems that “only use the dbo schema” don’t really.

End statements with semicolons;

sp_msforeachdb 'USE [?]; PRINT ''?'';'

Again something that is a good habit regardless. But since in dynamic SQL you may not have the normal line feeds/carriage returns that let SQL know that this is the end of a statement it’s a good idea include these as a habit. You probably won’t need them, but if you always put them in then on the odd occasion when you do, they will already be there.

Double, Triple and Quadruple check your quotes

SET @stringvar = 'It''s'

This one is hard enough I wrote an article “Single Quotation Marks in SQL” to try to help people with it. Getting the quotes right is possibly the hardest part of dynamic SQL. Double the quotes when you write dynamic SQL, but then you have dynamic SQL creating dynamic SQL, now it’s quadruple the quotes. Add another layer and it’s 8 times the quotes. It can get ugly really really quickly.

Format your code so that the output is formatted the way you want to read it

DECLARE @SQL nvarchar(max)
DECLARE @ProductID int
DECLARE @MaxQuantity int
DECLARE @MinQuantity int
SET @ProductID = 4
SET @MinQuantity = 1
SET @MaxQuantity = 100

SET @SQL = 
'SELECT Product.Name, Product.ProductNumber, ' + CHAR(13) + 
' ProductInventory.LocationID, ' + CHAR(13) + 
' ProductInventory.Quantity ' + CHAR(13) + 
'FROM [Production].[Product] ' + CHAR(13) + 
'JOIN [Production].[ProductInventory] ' + CHAR(13) + 
' ON Product.ProductID = ProductInventory.ProductID ' + CHAR(13) + 
'WHERE 1=1 ' + CHAR(13) + 
ISNULL(' AND Product.ProductID = '+CAST(@ProductID AS varchar) + CHAR(13), '') +
ISNULL(' AND ProductInventory.Quantity >= '+CAST(@MinQuantity AS varchar) + CHAR(13), '') +
ISNULL(' AND ProductInventory.Quantity <= '+CAST(@MaxQuantity AS varchar) + CHAR(13), '')

PRINT @SQL

SQL could care less about formatting, and its dynamic code that no one is going to read, so why format it? Because at some point someone may read it. Maybe to debug a problem, maybe just to try to figure out what you are doing. But somewhere along the line someone is going to want to read it and they will very much appreciate you having formatted it ahead of time. And to be fair I’m a fanatic about formatting my code. Everyone has to have their quirks right?

Well, that’s it. Those are my suggestions. Stick to them and hopefully you will run into fewer issues with your Dynamic SQL. I should note that I play with Dynamic SQL because I find it interesting and a bit of a challenge at times. As a general case dynamic SQL should be one of your last resorts. Unless you have a good reason to use dynamic SQL or don’t have another method that will work, then you should probably skip it and use another method.

NOTE: Some of you may notice I didn’t include using sp_executesql as one of my recommendations even though it’s a good idea and a very important way to avoid SQL injection. I will be talking about it later, I just didn’t feel it fit in with the rest of the these ideas.

About these ads

12 thoughts on “Best practice recommendations for writing Dynamic SQL

  1. Filipe says:

    I find it interesting that you use only to format you dynamic SQL. I have had problems with that practice, when I want to display the code and then copy and paste to execute, so I got into the practice of always using the CR+LF combination and never had problems after that.

    • I used to use both CR & LF but found over time that CHR(13) was enough when I was cutting and pasting into a query window. Also when I used both I occasionally would get double line breaks if I was putting it into other editors.

  2. TSQL hack, decent pilot says:

    All excellent points for writing dynamic SQL. I would just add that for production support of dynamic SQL nothing beats logging the actual statements being run to a file or table. The only thing that’s worse than debugging dynamic sql errors when you aren’t seeing what it’s actually running is debugging bitwise coding. Great stuff!

    • I think that is a great suggestion. I wouldn’t want to do it all the time because it would add a bit of a delay to the process. Also if I’m running the code frequently enough, 100′s of times a minute for example, then I might run into some concurrency issues. Usually if the error is recent enough I can pull what I need out of the DMVs.

  3. Charles Kincaid says:

    Wonderful article.
    I have taken to putting brackets around all identifiers. I upper case SQL reserved words that are not used as identifiers (we have tables [Order] and [User]).
    The SELECT is on a line by itself and each column is on its own line
    FROM is in the left column. Each join is on a line by itself and indented a couple of spaces.
    Same thing for WHERE, GROUP, HAVING and ORDER.

    These practices make changes easier.

    • Thanks :). I’m a big proponent of formatting although when I push for it I try to tell people be reasonably consistent and be readable. Beyond that I don’t really care what formatting they use, as long as they use something.

  4. These are great recommendations, thank you for sharing!

    Although you will probably never need it, for the most robust code, consider using the QUOTENAME() function instead of hard coding brackets. For example:

    – Instead of:
    SELECT ‘EXEC sp_spaceused ['+TABLE_SCHEMA+'.'+TABLE_NAME+'];’
    FROM INFORMATION_SCHEMA.TABLES

    – Consider:
    SELECT ‘EXEC sp_spaceused ‘+QUOTENAME(TABLE_SCHEMA+’.’+TABLE_NAME)+’;’
    FROM INFORMATION_SCHEMA.TABLES

    This will handle the very rare cases when a table name has a square bracket in it.

    When I write a stored procedure that uses complex dynamic SQL, I usually include a “@debug” bit parameter that, when set to 1, prints dynamic SQL before executing it. This has made debugging easier for me. For example:

    SET @sql = ‘…’

    IF @debug = 1 print @sql
    EXEC (@sql)

    • I really like the QUOTENAME suggestion. I haven’t run into cases with []s in the name but I could see how I could. I don’t usually add in a @debug variable just because it seems to add to the complexity. I keep a commented out print statement and if I need to debug I uncomment it and comment out the EXEC as needed. But again, that’s just personal preference :)

  5. George says:

    DECLARE @SQL nvarchar(max) = ”
    DECLARE @CrLf char(1) = CHAR(13)

    SET @SQL = @SQL + ‘SELECT Product.Name, Product.ProductNumber, ‘ + @CrLf
    SET @SQL = @SQL + ‘ProductInventory.LocationID, ‘ + @CrLf

  6. Dan Lavigueur says:

    I have written some pretty intense dynamic T-SQL. It doesn’t break often.

    I use all of the practices that you recommend, especially the formatting. It is a great aid in debugging since you can just capture the dynamically generated SQL to debug it; and it actually makes sense.

    I also wrap everything with sp_executesql. It’s just safer.

  7. […] little while back I wrote Best practice recommendations for writing Dynamic SQL where I made a number of suggestions for good habits when writing dynamic SQL. Along the same […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 173 other followers

%d bloggers like this: