Friday, January 11, 2008

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!

0 comments: