Setting a variable from dynamic SQL

I don't have to do this very often, but it definitely 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 output parameter from 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 example where this was useful for me was for converting a demormalized set of horizontal data to a normalized set of vertical data.

 

The denormalized data contained a series of column names such as "200701", "200702", "200703", etc. for each month of the year. The file changes month to month and to minimize re-writing code each time a new file comes in I am able to import the data generically, determine the columns that are in the file, and get the value by setting a variable using dynamic SQL.

 

-- declare variables

DECLARE @Total FLOAT

DECLARE @SqlStatement NVARCHAR(1000)

 

-- set default values

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

 

Enjoy!