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.

Filed under: Dynamic SQL, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, dynamic sql, language sql, microsoft sql server, sql statements, T-SQL