Output Parameter for sp_executesql

sp_executesql is the preferred way for running dynamic queries because it encourages parametization which in turn increases the chances of plan reuse.

Here I will discuss a gotcha when using sp_executesql with output parameters and in a loop. Instead of initializing the output parameters in each iteration of the loop, the value from the previous iteration is kept. Let's look at this simple example:

---- Script 1: Output parameter is not initialization
DECLARE @iCount INT, @vcSQL NVARCHAR(MAX), @iOut INT;
SELECT @iCount = 1;
WHILE (@iCount <= 2)
BEGIN
SELECT @vcSQL =
'
SELECT @i = COALESCE(@i, @j);
';
EXEC sp_executesql @vcSQL,
N'@i INT OUT, @j INT',
@iOut OUT, @iCount;
SELECT @iCount, @iOut;
SELECT @iCount = @iCount + 1;
END;

In Script 1, we have a WHILE loop for 2 iterations. The simple dynamic query as defined in @vcSQL has two parameters @i and @j as indicated in the execution of sp_executesql , with @i the OUTPUT parameter and @j the normal input parameter.
In the first iteration, @iCount = 1 which is passed to parameter @j. @i starts as NULL and therefore is assigned @j = 1. So, SELECT @iCount, @iOut; produces (1, 1).
In the second iteration, @iCount = 2 which again is assigned to @j, if @i were initialized, then just as in the first iteration, @i would be assigned @j=1, and SELECT @iCount, @iOut; would produce (2, 2). But actually, the result is (2, 1). That shows that at the start of iteration 2, @i was not initialized to NULL, but kept its previous value of 1 from iteration 1.

To make sure the output parameter is initialized in each iteration, we need to explicitly assign it a NULL value at the beginning inside the dynamic query as shown in Script 2. Running it produces the desired (1, 1) and (2, 2) from SELECT @iCount, @iOut; after the two iterations.

---- Script 2:
DECLARE @iCount INT, @vcSQL NVARCHAR(MAX), @iOut INT;
SELECT @iCount = 1;
WHILE (@iCount <= 2)
BEGIN
SELECT @vcSQL =
'
SELECT @i = NULL;
SELECT @i = COALESCE(@i, @j);
';
EXEC sp_executesql @vcSQL,
N'@i INT OUT, @j INT',
@iOut OUT, @iCount;
SELECT @iCount, @iOut;
SELECT @iCount = @iCount + 1;
END;

The above is a very simple demonstration. In the real world, @i might participate complex calculations. For example, it might be an output parameter of a stored procedure such as @expected_rowcount of sp_table_validation which behaves differently depending on whether or not @expected_rowcount is NULL. Knowing the difference with and without the explicit initialization will help us achieve the desired results.