Well-Treaded
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.
Mindblowing.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
Related Posts
- Don’t Rely On Square Brackets To Protect You From SQL Injection: Use QUOTENAME Instead
- Signs You Need Dynamic SQL To Fix Query Performance Problems In SQL Server
- SQL Server IF Branches And Query Performance Part 5: Does Dynamic SQL Work?
- SQL Server IF Branches And Query Performance Part 4: Do Stored Procedures Work?
Sure, but I mean, output parameters are always input parameters too — same with stored procedures 🙂 It’s just pass-by-reference.