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. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.