Mission: SQL Homeostasis: Writing Better Dynamic SQL

Writing Better Dynamic SQL

This updated, somewhat informal style guide is for SQL developers and DBAs, really, anyone brave enough to wrestle with dynamic SQL without losing their mind. Whether you are writing the code, reviewing it, or cleaning up after it, these practices can save you time, reduce risk, and make the next person’s job a little easier.

Although this article is mainly about writing code, it is useful for DBAs too. A good DBA does not need to be a full-time application developer, but should be comfortable reading and understanding code, especially the SQL being sent to the database. DBAs are often asked to troubleshoot slow queries, blocking, deadlocks, failed deployments, permissions issues, and unexpected plan changes. Being able to recognize unsafe or overly complicated dynamic SQL makes those conversations with developers much more productive and helps prevent problems before they reach production.

It is also worth remembering that dynamic SQL is not limited to scripts that explicitly use EXEC or sp_executesql inside SQL Server. Many applications build SQL statements before sending them to SQL Server. If application code inserts user entered values, selected filters, sort options, table names, or other input directly into a query string, that is dynamic SQL in practice. The same rules still apply: parameterize values, validate anything that cannot be parameterized, and avoid allowing user input to control SQL syntax unchecked.

Writing code that actually works, runs fast, and doesn’t explode with bugs is great, we all want that. But writing code that you (or anyone else) can still understand six months later is just as important.

This is especially true for dynamic SQL. 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 (SQL injection!)
Performance - or lack thereof.

There is general advice online suggesting that, to avoid SQL injection risks, you should use stored procedures within your application, in other words, never use plain SQL queries. Stored procedures are often presented as if they completely eliminate SQL injection risks. That is not accurate. While stored procedures can help reduce risk, they are not a guaranteed fix for SQL injection.

To effectively prevent SQL injection, you need to understand what actually causes it and how it works. Without going into a full tutorial, SQL injection typically occurs when user input is concatenated into a SQL string that is then executed using EXEC or sp_executesql. This pattern is, by definition, dynamic SQL. See Microsoft's SQL injection documentation.

That is the biggest reason why you should always follow the best practices described here when writing dynamic SQL. However, this should not be interpreted as permission to use dynamic SQL freely throughout your code. Use it only when you have a genuine use case and no viable alternatives exist.

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.

So, here are a few practical tips or best practices for writing dynamic SQL, because if we’re going to do something risky, we might as well do it semi-responsibly.

1. Document for Maintainability

Add a few useful comments around your dynamic SQL masterpiece. Explain why it needs to be dynamic, what the inputs mean, and which assumptions the next DBA should not accidentally break. Debugging is enough work without turning it into a treasure hunt.

Dynamic SQL deserves extra documentation because there are two things to understand: the code that builds the statement and the statement that eventually runs. With sp_executesql, that generated statement executes as a separate batch and cannot directly access variables declared in the calling batch A useful comment connects those pieces so the reader does not have to reconstruct the design from string fragments and parameter assignments.

Focus your comments on the decisions that are not obvious from the syntax:

Why it is dynamic: Identify what must change at runtime and why a static query is not sufficient.

What the inputs mean: Explain expected values, parameter mappings, and whether an input is a search value or the name of an object to query.

What the code assumes: Document the target database, expected permissions, dependencies, and any intentional limitations.

How to troubleshoot it: Explain how to inspect the generated statement and relevant inputs without exposing sensitive values in routine logs.

Adding a comment that simply states “-- Declare a variable” adds little beside a DECLARE statement. “Keep the search value out of the SQL text” explains a design decision worth preserving.

Example 1: Documenting parameterized dynamic SQL

This example intentionally uses dynamic SQL to demonstrate documentation and parameter passing, not because the lookup requires it. The query structure is fixed and only the search value changes, so the static version in Example 2 is the better choice for this task.

These are learning examples for a disposable database on SQL Server 2017 or later. Run them in the same SSMS query window after selecting that database; they create two ordinary stored procedures, which requires CREATE PROCEDURE permission in the database and ALTER permission on the target schema. The procedure bodies only read metadata, but the setup creates objects, and the optional cleanup removes those demo objects.

/*
Purpose:
  Demonstrate documentation and parameter passing with dynamic SQL.
  This lookup does not need dynamic SQL in production.

Input:
  @ObjectName is an unqualified name, not 'schema.object'.
  It is a search value, not an identifier inserted into SQL text.

Scope and limitations:
  Query sys.objects in the database containing this procedure.
  Return all visible matches; no schema filter is applied.
  NULL or an unmatched name returns no rows.

Diagnostics:
  @Debug = 1 prints the statement template, not parameter values.
  Execution still proceeds after printing.
*/
CREATE PROCEDURE dbo.GetObjectID_DynamicDemo
    @ObjectName SYSNAME,
    @Debug BIT = 0
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SqlCommand NVARCHAR(MAX) = N'
        SELECT object_id
        FROM sys.objects
        WHERE name = @NameParam;
    ';

    -- Opt-in diagnostics: sufficient for this short statement.
    IF @Debug = 1
        PRINT @SqlCommand;

    -- Declare the generated batch's parameter, then bind the
    -- procedure input to it. Do not concatenate the input into SQL.
    EXEC sys.sp_executesql
        @stmt = @SqlCommand,
        @params = N'@NameParam SYSNAME',
        @NameParam = @ObjectName;
END;
GO

-- Look up the procedure just created.
EXEC dbo.GetObjectID_DynamicDemo
    @ObjectName = N'GetObjectID_DynamicDemo',
    @Debug = 1;
GO

Read the execution call as three connected pieces: @stmt supplies the SQL text, @params declares @NameParam inside that text, and @NameParam = @ObjectName supplies its value. The different names make the handoff visible; they are not required to differ.

The protection comes from keeping the input separate from executable SQL text, not merely from calling sp_executesql; concatenating untrusted input into the statement can still introduce SQL injection, where input becomes executable code. Treat the comment about parameter binding as a guardrail for future edits, not as decoration.

The debug output intentionally retains @NameParam; it does not produce a statement with the supplied value substituted into the text. Also, PRINT truncates Unicode output beyond 4,000 characters, so do not treat it as a complete capture mechanism for long generated batches.

Both examples use sys.objects, which describes user-defined, schema-scoped objects and exposes metadata according to the caller's permissions. Keep the documented limitations in mind: these examples deliberately omit a schema filter, and an empty result is not proof that an object does not exist.

Example 2: The same lookup without dynamic SQL

For this lookup, there is no need to build a command string or maintain a second parameter declaration. Keep the input contract and limitations documented, but remove the unnecessary dynamic layer.

/*
Purpose:
  Return object IDs for visible matches in this procedure's database.

Input and limitations:
  @ObjectName is an unqualified name, not 'schema.object'.
  No schema filter is applied; return all visible matches.
  NULL or an unmatched name returns no rows.

Design:
  Only the search value changes, so use a static query.
*/
CREATE PROCEDURE dbo.GetObjectID_StaticDemo
    @ObjectName SYSNAME
AS
BEGIN
    SET NOCOUNT ON;

    SELECT object_id
    FROM sys.objects
    WHERE name = @ObjectName;
END;
GO

-- Use the same lookup value to compare the two versions.
EXEC dbo.GetObjectID_StaticDemo
    @ObjectName = N'GetObjectID_DynamicDemo';
GO

-- Optional cleanup: removes only these two demo procedures.
-- DROP PROCEDURE dbo.GetObjectID_DynamicDemo;
-- DROP PROCEDURE dbo.GetObjectID_StaticDemo;

Document the reasoning, not every keystroke. The next DBA should be able to see why dynamic SQL exists, how inputs reach the generated statement, and what must remain true when the code changes. If there is no reason for the dynamic layer, simplifying it is an even better maintainability improvement.

2. Prefer Parameterized Execution

Great advice, but how?

When dynamic SQL needs a search value, such as a customer name or an order date, keep that value separate from the command string. That is what parameterized dynamic SQL means: the statement contains a named placeholder, and you supply its value separately when executing it. In T-SQL, sys.sp_executesql supports this by accepting the SQL text, a definition of its parameters, and the values to pass into them.

Why does that matter? If you concatenate input into a command string, that input becomes part of the SQL text being executed. With parameter binding, SQL Server treats the supplied value as data, not additional SQL instructions, even if it contains quotation marks or SQL-looking text. The protection comes from keeping the value separate, not simply from calling sp_executesql.

Example: Pass the value separately

This SQL Server 2017-and-later example searches sys.objects, a catalog view describing user-defined, schema-scoped objects; the name column uses SYSNAME, and the rows you can see depend on your metadata permissions. Run it in the database you want to inspect, replacing Orders with the unqualified name of an existing user object you can see. The query only reads metadata and may return no rows or multiple matches because it does not filter by schema.

DECLARE @SqlCommand NVARCHAR(MAX) = N'
    SELECT object_id, schema_id, name, type_desc
    FROM sys.objects
    WHERE name = @ObjectName;
';

DECLARE @SearchName SYSNAME = N'Orders';

EXEC sys.sp_executesql
    @stmt = @SqlCommand,
    @params = N'@ObjectName SYSNAME',
    @ObjectName = @SearchName;
GO

Here, @ObjectName is a value being compared with the name column. It is not replacing a table or column name in the SQL syntax; those identifiers cannot be supplied through ordinary value parameters.

This lookup deliberately uses dynamic SQL to demonstrate parameter passing. Its query structure is fixed, so an ordinary static query would be simpler for the actual task.

Why this also helps plan reuse

An execution plan is SQL Server's strategy for running a query. If the next call searches for Customers instead of Orders, the parameterized statement still contains WHERE name = @ObjectName; only the supplied value changes, making reuse of an existing plan more likely and potentially avoiding additional compilation work.

Keep the statement text and parameter definitions consistent when only the values need to change. Think of this as encouraging plan reuse, not guaranteeing it or guaranteeing that every execution will be faster.

Contrast: Concatenating the value into the command

The following example instead places the name directly inside the SQL string. The hard-coded value is not itself an attack, but this is the pattern to avoid when the value comes from untrusted input.

-- Unsafe pattern when @SearchName comes from untrusted input.
-- Shown for comparison; execution is intentionally commented out.
DECLARE @SearchName SYSNAME = N'Orders';

DECLARE @SqlCommand NVARCHAR(MAX) = N'
    SELECT object_id, schema_id, name, type_desc
    FROM sys.objects
    WHERE name = N''' + @SearchName + N''';
';

PRINT @SqlCommand;
-- EXEC (@SqlCommand);
GO

In this version, changing the value also changes the command text. More importantly, if untrusted input is concatenated this way, an attacker may be able to alter the SQL; replacing EXEC with sp_executesql without removing that unsafe concatenation would not fix the problem.

Keep values as values, not pieces of executable SQL. Prefer sp_executesql with explicit parameter binding when your dynamic T-SQL needs input values, and document separately any parts of the query structure that genuinely need to change.

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

Besides the obvious security reasons (SQL Injection), there are other reasons why this is very important.

When the final command length may be large or unpredictable, use NVARCHAR(MAX) for the command variable and make sure intermediate string expressions are also wide enough. Otherwise, SQL Server may silently truncate part of the generated statement.

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.

The following example shows how to handle strings properly in dynamic SQL, avoiding truncation, NULL chaos, and other developer regrets. It follows best practices for managing strings safely and cleanly in dynamic SQL construction.

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

“Trusting any and all inputs” is not a security strategy. Use QUOTENAME() when you must place an identifier, such as a schema, table, or column name, into a dynamic statement. It safely delimits the identifier, but it should still be paired with validation. For example, validate a requested sort column or table name against a known allow-list before including it in the command.

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

In most cases, avoid manually escaping single quotes by passing values as parameters through sp_executesql. If you have a rare situation where a literal must be embedded in the generated SQL text, double embedded single quotes carefully and validate the result. Manual escaping should be the exception, not the default pattern.

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

Dynamic SQL is a powerful tool that can be incredibly helpful, until it gives you headaches you didn’t ask for. Treat it with respect: comment generously, use parameters, quote your object names properly, and debug like your sanity depends on it (because it probably does). Most disasters you hear about with dynamic SQL probably happened because someone, likely yourself, ignored these rules. So code defensively, document liberally, and maybe keep a stress ball handy.

Mission: SQL Homeostasis

Search This Blog

Tuesday, November 18, 2025