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!
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.
- T-SQL Tuesday: Dynamic SQL, The Data Type
- Stored Procedures vs sp_executesql In SQL Server: Is One Better Than The Other?
- SQL Server 2022 Parameter Sensitive Plan Optimization: Does PSP Work With Dynamic SQL?
- SQL Server 2022 Parameter Sensitive Plan Optimization: The Problem With Sniffed Parameter Sensitivity