Dynamic SQL
Execute some SQL in the current database context ….
EXEC sys.sp_executesql @stmt = <sql>, @params = <params>, @param1 = <value1>, ..., @paramN = <valueN>
@sql and @params are of type nvarchar and must be declared as such. If you supply string literals as parameters then they must be prefixed with N.
Example 0
-- This is a common dynamic SQL usecase since "CREATE SCHEMA must be the first statement in a query batch"
EXEC sys.sp_executesql N'CREATE SCHEMA foobar';
Example 1
DECLARE @fieldName nvarchar(max) = N'foo';
DECLARE @tableName nvarchar(max) = N'bar';
DECLARE @sql nvarchar(max);
SET @sql = 'SELECT ' + @fieldName + ' FROM ' + @tableName;
EXEC sys.sp_executesql @sql;
-- or ...
EXEC sys.sp_executesql @stmt = @sql; -- This will avoid any warnings about non-named params
Example 2
DECLARE @sql nvarchar(max);
DECLARE @params nvarchar(max);
SET @sql = 'SELECT foo, bar FROM some_table WHERE id = @id AND type = @type';
SET @params = '@id int, @type int';
EXEC sys.sp_executesql @stmt = @sql, @params = @params, @id = 123, @type = 42;
Example 3 - With output parameter
DECLARE @sql nvarchar(max);
DECLARE @params nvarchar(max);
SET @sql = 'SELECT @foo = foo FROM some_table WHERE id = @id';
SET @params = '@id int, @foo nvarchar(32) OUTPUT';
EXEC sys.sp_executesql @stmt = @sql, @params = @params, @id = 123, @foo OUTPUT;
Execute some SQL in a specific database context …
EXEC database.sys.sp_executesql @stmt = <sql>, @params = <params>, @param1 = <value1>, ..., @paramN = <valueN>
Execute some SQL in a specific database context where the database name is a run-time parameter …
DECLARE @database sysname = 'scratch';
DECLARE @sp_executesql nvarchar(max) = @database + '.sys.sp_executesql';
EXEC @sp_executesql @stmt = @sql, ...;
Example 4 - Normalize a proc name in a database
DECLARE @database sysname = 'scratch';
DECLARE @sp_executesql nvarchar(max) = @database + '.sys.sp_executesql';
DECLARE @procName sysname = 'TestProc';
SELECT @procName;
DECLARE @sql nvarchar(max);
-- MIN will return NULL if the proc doesn't exist
SET @sql = '
SELECT @procName = MIN(QUOTENAME(s.name) + ''.'' + QUOTENAME(o.name))
FROM sys.objects AS o INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE o.object_id = OBJECT_ID(@procName)';
EXEC @sp_executesql @sql, N'@procName sysname OUTPUT', @procName OUTPUT;
SELECT @procName;
Example 5 - Create a schema in a database …
DECLARE @database sysname = 'scratch';
DECLARE @sp_executesql nvarchar(max) = @database + '.sys.sp_executesql';
DECLARE @sql nvarchar(max);
SET @sql = 'CREATE SCHEMA foobar';
EXEC @sp_executesql @stmt = @sql;
Fun with parameters …
Example 6 - Error: Must declare the scalar variable “@number”
DECLARE @number int = 42;
DECLARE @sql nvarchar(max);
SET @sql = 'SELECT @number';
EXEC sp_executesql @sql;
Example 7 - Returns 42. Even though we don’t specifically supply a value for the parameter @number in the call to EXEC, this bringsthe variable into local scope and its current value becomes visible inside the dynamic SQL execution.
DECLARE @number int = 42;
DECLARE @sql nvarchar(max);
SET @sql = 'SELECT @number';
EXEC sp_executesql @sql, N'@number int', @number;
Example 8 - Returns 42. Another way of writing it, explicity providing the initial value of the @number parameter.
DECLARE @number int = 42;
DECLARE @sql nvarchar(max);
SET @sql = 'SELECT @number';
EXEC sp_executesql @sql, N'@number int', @number = @number;
Example 9 - Returns 43. The value of 42 is brought in from the outside then modified inside, and that modification is to the outside variable since the param was declared as OUTPUT.
DECLARE @number int = 42;
DECLARE @sql nvarchar(max);
SET @sql = 'SELECT @number = @number + 1';
EXEC sp_executesql @sql, N'@number int OUTPUT', @number OUTPUT;
SELECT @number;
Example 10 - Returns 43. Another way of writing it, explicity providing the initial value of the @number parameter.
DECLARE @number int = 42;
DECLARE @sql nvarchar(max);
SET @sql = 'SELECT @number = @number + 1';
EXEC sp_executesql @sql, N'@number int OUTPUT', @number = @number OUTPUT;
SELECT @number;