Another Reason Why I Love Dynamic SQL IN SQL Server: OUTPUT Parameters Can Be Input Parameters

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.