CODEWITHSUNDEEP
sqlsql-server-2008t-sqldynamic
buzzzzjay
buzzzzjay

Reputation: 1150

Dynamically adding columns to query

I am trying to create a dynamic query that selects a specific column based on an error code.

For Example I have an error table (ErrorTable) that contains multiple columns:

Transaction Number 
Transaction Amount
Transaction Date
Error Code
Error Description
+100 other columns

Errors Table

Error Code
Error Description
Error Column

I am trying to get a query that would get the following:

ErrorCode
ErrorDescription
ErrorColumn (Column Based On Error Code)

I tried using dynamic SQL like the following and still got just the name of the column returned, maybe I am doing something wrong?

DECLARE @SQL VarChar(1000)
SELECT @SQL = 'SELECT et.ErrorCode, et.ErrorDescription, e.ErrorColumn 
               FROM ErrorTable et
                   INNER JOIN Errors e ON e.ErrorCodeID = et.ErrorCode'
Exec ( @SQL)

If I use @ErrorColumn = 'TransactionDate' instead of e.ErrorColumn in the dynamic query I get results, but with the query above I don't. Any ideas?

Update 2

I get the following results with the above query:

ErrorCode    ErrorDesc              TransactionDate    TransactionAmount
1            Invalid Trans Date     TransactionDate    TransactionAmount
2            Invalid Trans Amount   TransactionDate    TransactionAmount

I want the following:

ErrorCode    ErrorDesc              TransactionDate    TransactionAmount
1            Invalid Trans Date     May 1st            
2            Invalid Trans Amount                      65 Cats

Upvotes: 1

Views: 1547

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

Are you meaning to do this:

DECLARE @ErrorColumn SYSNAME = N'TransactionDate';
-- presumably the above is a parameter to the procedure

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = N'SELECT et.ErrorCode, et.ErrorDescription, et.' 
  + QUOTENAME(@ErrorColumn)
  + ' FROM dbo.ErrorTable AS et
      INNER JOIN dbo.Errors AS e
      ON e.ErrorCodeID = et.ErrorCode;';

EXEC sp_executesql @sql;

Based on further information, perhaps what you want is this:

SELECT et.ErrorCode, et.ErrorDescription, 
  TransactionDate   = CASE et.ErrorColumn WHEN N'TransactionDate' 
    THEN e.TransactionDate   ELSE NULL END,
  TransactionAmount = CASE et.ErrorColumn WHEN N'TransactionAmount' 
    THEN e.TransactionAmount ELSE NULL END
FROM dbo.ErrorTable AS et 
INNER JOIN dbo.Errors AS e
ON et.ErrorCode = e.ErrorCodeID;

Or this:

SELECT et.ErrorCode, et.ErrorDescription, 
  TransactionDate   = CASE et.ErrorColumn WHEN N'TransactionDate' 
    THEN e.TransactionDate   ELSE '' END,
  TransactionAmount = CASE et.ErrorColumn WHEN N'TransactionAmount' 
    THEN e.TransactionAmount ELSE '' END
FROM dbo.ErrorTable AS et 
INNER JOIN dbo.Errors AS e
ON et.ErrorCode = e.ErrorCodeID;

If you need to only return the columns that actually exist in the data set, it is slightly more convoluted. You basically have to build dynamic SQL to include only the columns you have to reference in the eventual query.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += ',' + CHAR(13) + CHAR(10) 
  + et.ErrorColumn + ' = CASE et.ErrorColumn WHEN N''' 
  + et.ErrorColumn + ''' THEN e.' + et.ErrorColumn + ' ELSE NULL END'
FROM dbo.ErrorTable AS et INNER JOIN dbo.Errors AS e
ON et.ErrorCode = e.ErrorCodeID
GROUP BY et.ErrorColumn;

SELECT @sql = N'SELECT et.ErrorCode, et.ErrorDescription' 
  + @sql + '
  FROM dbo.ErrorTable AS et 
INNER JOIN dbo.Errors AS e
ON et.ErrorCode = e.ErrorCodeID;';

PRINT @sql;

-- EXEC sp_executesql @sql;

Upvotes: 1

Related Questions