SQL Server Interview Question #119

What is dynamic SQL?

Views, Stored Procedures & User-Defined Functions Mid-Level Intermediate

Detailed Explanation

Dynamic SQL is T-SQL constructed and executed at runtime. It is useful when query structure itself must vary, such as dynamic column selection, optional object names, or certain advanced search/reporting requirements.

SQL Server provides EXEC and sp_executesql. sp_executesql is generally preferred when user-supplied values can be parameterized because it improves security and can support execution-plan reuse.

Object names such as table or column names cannot be passed as ordinary query parameters; when dynamic identifiers are required, they must be validated and safely delimited, commonly with QUOTENAME.

Code Example

DECLARE @Sql NVARCHAR(MAX) =
    N'SELECT Id, Name, Price
      FROM dbo.Products
      WHERE CategoryId = @CategoryId;';

EXEC sys.sp_executesql
    @Sql,
    N'@CategoryId INT',
    @CategoryId = 10;