This updated and "sort" of a style guide is for SQL developers and, to some extent, DBAs, essentially anyone daring (and sometimes foolish) enough to wrestle with dynamic SQL without losing their mind. Whether you’re writing the code or cleaning up after it, these tips might save you some sleepless nights and a few colorful words. No promises, but at least you’ll have done your best to make someone’s life, and maybe your own, a little less miserable in the future.
Writing code that actually works, runs fast, and doesn’t explode with bugs is great, we all want that. But let's be honest, writing code that you (or anyone else) can still understand six months later is just as important.
Now, let’s talk about dynamic SQL. Or rather, let’s not. Bring it up in some circles, and you might get the same reaction as announcing you still use tabs instead of spaces (developer humor). And many people (mainly DBAs) will tell you to avoid it like the plague, and they’re not entirely wrong, for two very good reasons:
- Security - hello, SQL injection!
- Performance - or lack thereof.
Think about the last time you had to fix someone else’s “clever” code. Even well-documented scripts can be confusing enough. Add dynamic SQL to the mix, and it starts feeling like a book that keeps shuffling its chapters every time you read it. Back in my younger, supposedly dazzling days, someone once described my dynamic SQL code as “very eloquent.” I took it as a compliment at the time, though, looking back, I’m not entirely sure it was.
1. Document for Maintainability
/* EXAMPLE *************** Stored procedure to safely query Object ID by Name using dynamic SQL. Design considerations: - Uses sp_executesql for parameterization to prevent SQL injection. - Accepts user input as a parameter rather than string concatenation. - Uses QUOTENAME if needing to inject object names to avoid SQL injection. - Prints the constructed SQL string for debugging purposes. */ IF OBJECT_ID('tempdb..#GetObjectID') IS NOT NULL DROP PROCEDURE #GetObjectID GO CREATE PROCEDURE #GetObjectID @ObjectName NVARCHAR(50) AS BEGIN -- Declare variable to hold dynamic SQL DECLARE @SqlCommand NVARCHAR(MAX); -- Build parameterized SQL query to select Object ID, filtering by Object Name SET @SqlCommand = N' SELECT object_id FROM sys.objects WHERE name = @NameParam; '; -- Debug print of the SQL command string PRINT 'Executing SQL: ' + @SqlCommand; -- Execute dynamic SQL with parameter safely passed in EXEC sp_executesql @SqlCommand, N'@NameParam NVARCHAR(50)', @NameParam = @ObjectName; END; GO
-- TestEXEC #GetObjectID 'sysdbfiles'
2. Prefer Parameterized Execution
Great advise, but how?
When executing dynamic SQL, always use sp_executesql with parameters instead of EXEC. It lowers your SQL injection risk, makes SQL Server actually reuse execution plans, and lets you have nice things like output variables.
This example fetches a record from the sys.objects table using proper parameters, which is just a fancy way of saying “without inviting disaster.”
DECLARE @SqlCommand NVARCHAR(MAX) = N'SELECT * FROM sys.objects WHERE name = @ObjectName;'; DECLARE @ObjectName NVARCHAR(50) = 'sysdbfiles'; EXEC sp_executesql @SqlCommand, N'@ObjectName NVARCHAR(50)', @ObjectName;
This next example qualifies as a public service announcement on what *not* to do:
-- BAD - DO NOT USE **************DECLARE @ObjectName NVARCHAR(50) = 'sysdbfiles'; DECLARE @SqlCommand NVARCHAR(MAX) = N'SELECT * FROM sys.objects WHERE Name = ''' + @ObjectName + N''''; EXEC(@SqlCommand);
3. Avoid Unnecessary Dynamic SQL
- Treat dynamic SQL like hot sauce: a little goes a long way, and too much will set everything on fire. Only use it when table, column, or object names actually require it, not just because typing EXEC is too convenient and powerful.
- For filters and conditions, stick with parameterized T‑SQL or stored procedures. They may be boring, but “boring and secure” beats “exciting and hacked” any day.
4. Manage String Handling Carefully
- When working with command strings, use NVARCHAR(MAX). Otherwise, enjoy the mystery of why half your query disappears into thin air.
- Watch out for NULLs in concatenation, ISNULL, COALESCE, or CONCAT are your friends here. Pretend you care now, or you’ll definitely care later when your query returns nothing and you have no idea why.
- And yes, use the built-in string functions (LTRIM, RTRIM, TRIM, CHARINDEX, STUFF, REPLACE, TRANSLATE, SUBSTRING, REPLICATE, REVERSE). They exist for a reason — mostly to save you from yourself.
/* Example: Building a dynamic query with safe string handling. - Uses NVARCHAR(MAX) to avoid truncation. - Handles NULL variables safely with ISNULL/COALESCE. - Demonstrates concatenation with CONCAT and "+" operator. - Uses sp_executesql to parameterize input safely. */ DECLARE @schema NVARCHAR(50) = NULL; DECLARE @table NVARCHAR(50) = 'sysfiles'; DECLARE @column NVARCHAR(50) = 'name'; DECLARE @value NVARCHAR(50) = 'master'; -- Demonstrate concatenation with NULLs handled explicitly DECLARE @sql1 NVARCHAR(MAX); SET @sql1 = 'SELECT * FROM ' + ISNULL(@schema + '.', '') -- Prevent NULL schema from breaking string + @table + ' WHERE ' + @column + ' = @filterValue'; -- Alternative using CONCAT which treats NULL as empty string DECLARE @sql2 NVARCHAR(MAX); SET @sql2 = CONCAT( 'SELECT * FROM ', COALESCE(@schema + '.', ''), -- COALESCE also protects NULL @table, ' WHERE ', @column, ' = @filterValue' ); -- Use sp_executesql with parameter to avoid injection and ensure plan reuse EXEC sp_executesql @sql1, N'@filterValue NVARCHAR(50)', @filterValue = @value; -- Output both SQL strings for debugging PRINT 'SQL using ISNULL concat: ' + @sql1; PRINT 'SQL using CONCAT: ' + @sql2;
Key Takeaways:
- Use NVARCHAR(MAX) for large dynamic strings. Otherwise, enjoy the thrill of wondering why half your SQL command just vanished mid‑execution.
- Use ISNULL or COALESCE to keep one pesky NULL from turning your entire concatenated string into nothingness, because apparently, NULL doesn’t believe in teamwork.
- Use CONCAT to make concatenation cleaner and to automatically treat NULLs like the empty shells they are. Fewer headaches, more functioning code.
- Parameterize values with sp_executesql. It keeps your code secure, faster, and less likely to turn into a free‑for‑all SQL injection party.
- Add some debug prints of your constructed SQL. It’s the developer equivalent of talking to yourself, slightly weird, but surprisingly effective when things stop making sense.
5. Debug Effectively
- Print your dynamic command strings while developing, it’s the SQL equivalent of talking to yourself, but at least this version occasionally answers back.
- If your dynamic statements are going get longer than a toddler’s bedtime story, bump up the text output limit in SQL Server Management Studio (Tools > Options > Query Results > Results to Text). Otherwise, you’ll get half a query and twice the confusion.
6. Object Naming and Injection Safety
- Always wrap dynamic object names with QUOTENAME to keep both syntax errors and unwelcome surprises out of your SQL.
- Avoid injecting raw table or column names directly, it’s faster to validate them against an allow‑list than to explain later why production went down during peak usage.
The example below shows how to manage object naming and user input in dynamic SQL the right way, efficient, secure, and refreshingly uneventful.
/* Example: Dynamic SQL with safe object naming and injection protection. - Uses QUOTENAME to safely delimit schema, table, and column names. - Prevents SQL injection via object names with QUOTENAME. - Uses sp_executesql with parameters for user inputs. */ DECLARE @SchemaName SYSNAME = 'dbo'; DECLARE @TableName SYSNAME = 'sysfiles'; DECLARE @ColumnName SYSNAME = 'name'; DECLARE @FilterValue NVARCHAR(50) = 'master'; DECLARE @Sql NVARCHAR(MAX); -- Build dynamic SQL with safely quoted object names SET @Sql = N'SELECT * FROM ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' WHERE ' + QUOTENAME(@ColumnName) + N' = @Dept'; -- Execute with parameter to avoid injection on values EXEC sp_executesql @Sql, N'@Dept NVARCHAR(50)', @Dept = @FilterValue; -- Optional: Print SQL string for debugging PRINT @Sql;Remember:
QUOTENAME()wraps object names in delimiters ([]) to prevent injection and syntax errors caused by spaces, special characters, or reserved keywords.Never directly concatenate user input into object names without validation and quoting.
Continue using parameterized queries (
sp_executesql) to safeguard user-supplied data.Always explicitly specify schema names for clarity and security.
7. Handle Single Quotes Properly
- If there’s one thing dynamic SQL loves, it’s breaking because of a single missing quote. Even to this day this happens to be almost every time I write dynamic SQL.
- Make sure to double up single quotes inside your SQL literals, yes, two of them. No, not sometimes. Always.
- Print your command strings often while debugging; it’s the only way to spot those sneaky quoting errors before they ruin your day (again).
The example below shows how to do it right, written by someone who learned this lesson the hard way, repeatedly.
/* Example: Handling single quotes dynamic SQL by doubling single quotes. - Uses REPLACE to escape single quotes by replacing each single quote with two. - Prevents syntax errors caused by unescaped single quotes. - Uses sp_executesql with parameters for safer execution when possible. */-- Create O'mighty O'sql table if doesn't already existIF OBJECT_ID('O''mighty O''sql') IS NOT NULL DROP TABLE [O'mighty O'sql] ; GO CREATE TABLE [O'mighty O'sql] (id int); GO DECLARE @UserInput NVARCHAR(50) = 'O''mighty O''sql'; -- Input with single quotes -- Unsafe dynamic SQL by direct concatenation (not recommended): DECLARE @SqlUnsafe NVARCHAR(MAX); SET @SqlUnsafe = 'SELECT * FROM sys.objects WHERE name = ''' + REPLACE(@UserInput, '''', '''''') + ''''; -- Double single quotes to escape PRINT 'Unsafe SQL: ' + @SqlUnsafe; EXEC(@SqlUnsafe); -- Better: Use sp_executesql with parameters to avoid manual escaping: DECLARE @SqlSafe NVARCHAR(MAX) = 'SELECT * FROM sys.objects WHERE name = @ObjectName'; PRINT 'Safe SQL: ' + @SqlSafe; EXEC sp_executesql @SqlSafe, N'@ObjectName NVARCHAR(50)', @ObjectName = @UserInput;GO-- Drop table O'mighty O'sqlIF OBJECT_ID('O''mighty O''sql') IS NOT NULL DROP TABLE [O'mighty O'sql] ;
Remember:
- When you’re dynamically concatenating strings that contain single quotes, use REPLACE(value, '''', '''''') to double them up. Yes, it looks ridiculous, and yes, it’s necessary, because SQL doesn’t share your sense of humor.
- Better yet, use sp_executesql with parameters and skip the manual quote juggling altogether. It’s cleaner, safer, and saves you from explaining to your team why your code exploded over one punctuation mark.
- Print out your SQL command as you go; it’s like holding up a mirror to your mistakes before they hit production.
Summary & Conclusion
Resources
Microsoft Article on sp_executesql
EXEC and sp_executesql – how are they different
Gotchas to Avoid for Better Dynamic SQL
Why sp_prepare Isn’t as “Good” as sp_executesql for Performance
