A lot has been written about dynamic SQL over the years, but I ran into a situation recently where I needed to rewrite some code that needed it with minimal disruption to other parts of a stored procedure.
The goal was to set a bunch of variables equal to column values in a table, but the catch was that some of the values that needed to be set also needed to be passed in as search arguments. Here’s a really simplified example:
DECLARE @i int = 4, @s nvarchar(MAX) = N''; SET @s += N' SELECT TOP (1) @i = d.database_id FROM sys.databases AS d WHERE d.database_id > @i ORDER BY d.database_id; ' EXEC sys.sp_executesql @s, N'@i INT OUTPUT', @i OUTPUT; SELECT @i AS input_output;
The result is this:
All Points In Between
Since we declare @i outside the dynamic SQL and set it to 4, it’s known to the outer scope.
When we execute the dynamic SQL, we tell it to expect the @i parameter, so we don’t need to declare a separate holder variable inside.
We also tell the dynamic SQL block that we expect to output a new value for @i.
While we’re also passing in @i as a parameter.
Thanks for reading!