
(Photo: Andy Mallon, Hoover Dam, Jan 2023)
One of my coworkers asked me about output parameters from dynamic SQL today. I pointed him at sp_executesql, which allows for passing parameters into or out of dynamic SQL. The syntax can be confusing at first blush (especially for a new DBA or developer), but it’s really pretty straightforward, easy, and useful.
It’s scary because there are a lot of variables. Often time, people use the same name for both inside the dynamic SQL & outside in your “regular” SQL. Sometimes you surround your parameters in quotes, and other times you don’t.
I whipped up a quick example for him, which he declared (I’m paraphrasing) “the easiest to understand example that I’ve seen for sp_executesql”:
DECLARE @i int,
@sql nvarchar(1000),
@params nvarchar(1000);
SET @params = N'@i_out int OUT';
SET @sql = N'SELECT @i_out = 1';
EXEC sys.sp_executesql @sql, @params, @i_out = @i OUT;
SELECT @i;The trick is to think of this as a dynamic stored procedure where the definition and procedure call all happen at the same time. @sql is the body of the stored procedure definition (i.e., the part after CREATE PROCEDURE...AS...). @params is the parameter definition list.

Then, when you call sp_executesql with parameters, remember to think of the parts in the same terms:
EXEC sys.sp_executesql
[stored proc definition],
[parameter definition list],
[parameter assignments];If you wanted to use multiple parameters, a slightly more complicated example would look something like this:
DECLARE @i int,
@x int,
@sql nvarchar(1000),
@params nvarchar(1000);
SET @params = N'@i_out int OUT, @x int';
SET @sql = N'SELECT @i_out = 1 WHERE @x = 2';
EXEC sys.sp_executesql @sql, @params, @i_out = @i OUT, @x = 3;
SELECT @i;
EXEC sys.sp_executesql @sql, @params, @i_out = @i OUT, @x = 2;
SELECT @i;