7 questions found
What is dynamic SQL, and why would you need to build and execute a SQL statement as a string rather than writing it directly?
Beginner
Dynamic SQL means building a SQL statement as a text string at runtime and then executing it, which is useful when parts of your query, such as the table name or the columns being sorted by, are not known ahead of time and depend on some condition or user input determined while the program is running.
DECLARE @TableName NVARCHAR(100) = 'Orders';
DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM ' + @TableName;
EXEC (@SQL);
Real-world example
A reporting tool lets users choose which table to generate a report from through a dropdown menu, using dynamic SQL to build the correct query based on whichever table the user actually selected.
Common follow-ups: What are the security risks associated with building dynamic SQL from user input?;What is the difference between using EXEC and sp_executesql to run dynamic SQL?
Stored Procedures & Functions;Error Handling with TRY CATCH
What is SQL injection, and how does it relate to writing dynamic SQL unsafely?
Beginner
SQL injection is a security vulnerability where an attacker inserts malicious SQL code into an input field, which then gets concatenated directly into a dynamic SQL string and executed as part of your query, potentially letting them view, modify, or delete data they should never have access to.
-- Vulnerable: directly concatenating user input
DECLARE @UserInput NVARCHAR(100) = 'Test'' OR ''1''=''1';
DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Users WHERE Name = ''' + @UserInput + '''';
-- This could return all rows instead of just matching ones
Real-world example
A poorly built login form that concatenates a username directly into a dynamic SQL query is exploited by an attacker who enters a specially crafted value, bypassing authentication entirely due to SQL injection.
Common follow-ups: How does using parameterized queries prevent SQL injection?;What other precautions help protect against SQL injection beyond avoiding string concatenation?
SQL Server Security & Permissions;Stored Procedures & Functions
How do you safely use sp_executesql to execute dynamic SQL with parameters, avoiding the risks of simple string concatenation?
Intermediate
sp_executesql lets you define a parameterized dynamic SQL statement and pass in parameter values separately, similar to how a regular stored procedure works, which both protects against SQL injection and allows SQL Server to reuse cached execution plans for better performance compared to plain string concatenation.
DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM Orders WHERE CustomerId = @CustId';
EXEC sp_executesql @SQL, N'@CustId INT', @CustId = 123;
Real-world example
A reporting application safely filters orders by a customer id provided by the user, using sp_executesql with a proper parameter instead of concatenating the value directly into the SQL string, protecting against injection attacks.
Common follow-ups: Why does using parameters also improve performance through plan reuse?;What data types can be used as parameters with sp_executesql?
Query Optimization & Plans;SQL Server Security & Permissions
How would you build a dynamic SQL query where the table or column names themselves need to change based on input, since parameters cannot be used for object names?
Intermediate
Since parameters in sp_executesql can only substitute for values and not for object names like tables or columns, you must carefully validate and sanitize any user provided table or column names, often checking them against a known allow list, before safely concatenating them into your dynamic SQL string using functions like QUOTENAME to properly escape them.
DECLARE @ColumnName NVARCHAR(128) = 'OrderDate';
DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM Orders ORDER BY ' + QUOTENAME(@ColumnName);
EXEC sp_executesql @SQL;
Real-world example
A reporting tool lets users choose which column to sort results by, validating the chosen column name against an allow list of known safe columns and using QUOTENAME before building the final dynamic SQL string.
Common follow-ups: What does the QUOTENAME function actually do to protect against injection?;Why is validating against an allow list important even when using QUOTENAME?
SQL Server Security & Permissions;Stored Procedures & Functions
How would you build a dynamic SQL statement inside a stored procedure that constructs a flexible search query based on several optional filter parameters?
Advanced
You build the base query as a string and conditionally append additional WHERE clause segments only for the filter parameters that were actually provided, using parameterized values throughout with sp_executesql, letting a single stored procedure handle many different combinations of search criteria without writing a separate query for every possible combination.
CREATE PROCEDURE SearchOrders
@CustomerId INT = NULL,
@Status VARCHAR(20) = NULL
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM Orders WHERE 1=1';
IF @CustomerId IS NOT NULL SET @SQL += N' AND CustomerId = @CustId';
IF @Status IS NOT NULL SET @SQL += N' AND Status = @Stat';
EXEC sp_executesql @SQL, N'@CustId INT, @Stat VARCHAR(20)', @CustId = @CustomerId, @Stat = @Status;
END;
Real-world example
A customer service tool uses a single flexible search stored procedure built with dynamic SQL, letting agents search orders by customer, status, or both together, without needing several separate hardcoded procedures for each combination.
Common follow-ups: What is the WHERE 1=1 trick used for in this kind of dynamic query building?;How do you handle a large number of optional filters without the dynamic SQL becoming difficult to maintain?
Stored Procedures & Functions;Query Optimization & Plans
What are the performance tradeoffs of using dynamic SQL compared to static, precompiled SQL statements?
Advanced
Dynamic SQL can sometimes generate a large number of slightly different query strings, each requiring its own separate execution plan to be compiled and cached, which can lead to plan cache bloat and reduced plan reuse, whereas well parameterized static SQL or properly parameterized dynamic SQL through sp_executesql allows SQL Server to reuse a single cached plan much more effectively.
-- Poorly parameterized dynamic SQL creates many unique plans
EXEC ('SELECT * FROM Orders WHERE CustomerId = ' + CAST(@Id AS VARCHAR));
-- Properly parameterized version reuses a single plan
EXEC sp_executesql N'SELECT * FROM Orders WHERE CustomerId = @Id', N'@Id INT', @Id;
Real-world example
A team notices their server's plan cache filling up with thousands of nearly identical dynamic SQL statements, and after switching to properly parameterized sp_executesql calls, the plan cache size and overall performance improve significantly.
Common follow-ups: How do you check the plan cache for signs of excessive unparameterized dynamic SQL?;What tools help identify queries that are not reusing execution plans effectively?
Query Optimization & Plans;SQL Server Profiler & Extended Events
How do you debug a dynamic SQL statement when it fails or does not produce the expected results?
Intermediate
A helpful technique is to print or select the final generated SQL string before actually executing it, letting you visually inspect the exact statement that would run and catch any syntax errors or unexpected values, which is often much easier than trying to debug the error message that comes back from executing a broken dynamic query directly.
DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM Orders WHERE CustomerId = @Id';
PRINT @SQL; -- inspect before executing
EXEC sp_executesql @SQL, N'@Id INT', @Id = 123;
Real-world example
A developer troubleshooting a dynamic SQL query that keeps failing adds a PRINT statement to see the exact generated SQL text, quickly spotting a missing space that was causing a syntax error.
Common follow-ups: What other debugging techniques help when working with complex dynamic SQL?;Should PRINT statements be removed before deploying dynamic SQL to production?
Error Handling with TRY CATCH;Stored Procedures & Functions