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.
|
1 |
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
|
1 2 |
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
|
1 2 |
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.
Continue reading on SQLStudies.com.