Writing Better Dynamic SQL

/*
  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.

• 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.

Copy
/*
  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.

• 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.

Copy
/*
  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 exist
IF 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'sql 
IF 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.  

This approach keeps single quotes from wrecking your dynamic SQL and, more importantly, keeps you from accidentally inventing new injection vectors in the name of “quick testing.”

Summary & Conclusion

Resources