Setting a variable from dynamic SQL

Matt Pavey

I do not have to do this often, but it comes in handy in certain situations.

Setting a variable from dynamic SQL:

DECLARE @MyValue INT

EXEC sp_executesql N'SELECT @MyValue = 999',
                   N'@MyValue INT OUTPUT',
                   @MyValue OUTPUT

SELECT @MyValue

Setting an output parameter from a dynamic stored procedure call:

DECLARE @OutputParameter VARCHAR(100)
DECLARE @Error           INT
DECLARE @SPName          VARCHAR(128)
DECLARE @SPCall          NVARCHAR(128)
DECLARE @RC              INT

SELECT @SPCall = 'EXEC ' + @SPName + ' @OutputParameter OUTPUT'

EXEC @RC = sp_executesql @SPCall,
                         N'@OutputParameter VARCHAR(100) OUTPUT',
                         @OutputParameter OUTPUT

SELECT @Error = @@ERROR

One place this was useful for me was converting a denormalized set of horizontal data into a normalized vertical set.

The denormalized data had a series of column names like "200701", "200702", "200703", one for each month of the year. The file changed month to month, and to avoid rewriting code every time a new one arrived, I could import the data generically, work out which columns were in the file, and pull each value by setting a variable with dynamic SQL.

DECLARE @Total        FLOAT
DECLARE @SqlStatement NVARCHAR(1000)

SET @Total = 0

SET @SqlStatement = 'SELECT @Total = [' + @ColumnName + '] ' +
                    'FROM RawData WHERE RecordID = ' + CONVERT(VARCHAR, @RecordID)

-- get the specified column value for the current record
EXEC sp_executesql @SqlStatement, N'@Total FLOAT OUTPUT', @Total OUTPUT