Dynamic SQL

26.1 EXEC vs sp_executesql

-- EXEC: no parameters, injection-prone, poor plan reuse
DECLARE @t SYSNAME = N'Customers';
EXEC (N'SELECT COUNT(*) FROM dbo.' + @t);   -- unsafe if @t comes from user input

-- sp_executesql: parameterized and safe (preferred)
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM dbo.Orders WHERE CustomerID = @cid AND Status = @st';
EXEC sp_executesql @sql, N'@cid INT, @st VARCHAR(20)', @cid = 1, @st = 'Pending';

-- Output parameter
DECLARE @cnt INT;
EXEC sp_executesql N'SELECT @c = COUNT(*) FROM dbo.Customers', N'@c INT OUTPUT', @c = @cnt OUTPUT;

26.2 SQL Injection Prevention

Parameterize values. Object names can't be parameterized. Use QUOTENAME() and validate them against sys.objects.

Least-privilege accounts. Never concatenate user input into SQL.

IF OBJECT_ID(N'dbo.' + QUOTENAME(@t)) IS NULL THROW 50000, 'Invalid table', 1;
SET @sql = N'SELECT COUNT(*) FROM dbo.' + QUOTENAME(@t);

26.3 Dynamic Search Conditions (Catch-All Queries)

DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM dbo.Orders WHERE 1 = 1';
IF @CustomerID IS NOT NULL SET @sql += N' AND CustomerID = @CustomerID';
IF @Status     IS NOT NULL SET @sql += N' AND Status = @Status';
EXEC sp_executesql @sql, N'@CustomerID INT, @Status VARCHAR(20)', @CustomerID, @Status;

26.4 Other Dynamic Execution

EXEC... AT LinkedServer EXECUTE AS context switching Temp-table scope: a #temp created inside dynamic SQL disappears when the dynamic batch ends.