SQL Server Dynamic SQL (sp_executesql)

SQL Server Dynamic SQL (sp_executesql) is a SQL statement in the SQL Server Specific category. SQL Server: safely executing dynamic SQL with parameterized statements. Prevents SQL injection in dynamic queries. The syntax is EXEC sp_executesql N'SELECT * FROM table WHERE col = @param', N'@param type', @param = value;. It returns dynamic SQL result. A typical example: DECLARE @tableName NVARCHAR(128) = N'employees'; DECLARE @sql NVARCHAR(MAX); -- Build dynamic query with QUOTENAME to prevent injection: SET @sql = N'SELECT name, salary FROM ' + QUOTENAME(@tableName) + N' WHERE department = @dept' EXEC sp_executesql @sql, N'@dept NVARCHAR(50)', @dept = N'IT'; -- Safe: parameters are typed and escaped -- Compare to vulnerable EXEC: -- EXEC('SELECT * FROM ' + @tableName); -- SQL injection risk! A close relative is OUTPUT Clause, which sQL Server: returns values from DML statements (like PostgreSQL RETURNING). A close relative is STUFF(), which sQL Server: deletes a specified length of characters from a string and inserts another string at the start position.