SQL Server › Dynamic SQL

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

Answer

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