Dynamically adding columns to query

Question

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

Your example select statement is invalid. You select ErrorCode without an aliased table prefix. Which table is that coming from? — VenerableAgents
– VenerableAgents, Commented Jun 6, 2012 at 18:24
If I'm reading this right, you have the name of the column stored in Errors.ErrorColumn and you want the error type to determine which column is selected. If the number of error types is not large, consider writing a CASE statement. Otherwise, you'll have to use dynamic SQL. — SQLCurious
– SQLCurious, Commented Jun 6, 2012 at 18:28
I'm with jonnyGold it looks like your query should be returning what you want. The join is correct and you are selecting the right columns. What aren't you able to get to work about it? — Tony318
– Tony318, Commented Jun 6, 2012 at 18:44

Aaron Bertrand · Accepted Answer · 2012-06-06 19:35:42Z

1

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;

edited Jun 6, 2012 at 19:35

answered Jun 6, 2012 at 18:47

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

buzzzzjay Over a year ago

This works correctly as expected, but I wasn't wanting to pass in a parameter. Is there a way to do this without passing in a parameter and just using the value in the Errors table? This is what I meant that I got it to to work @ErrorColumn, I was meaning that if you pass in a parameter it works as expected (ie. returning table values). Thanks!

Aaron Bertrand Over a year ago

Okay, please show some sample data and desired results - we would have never guessed this based on what you have in the question now. Do you want this result for a specific row, or for all rows? E.g. if one error says TransactionDate and another says TransactionNumber, you want the same column to show May 1 for one row and 12345 for the other row?

buzzzzjay Over a year ago

Updated OP with details. Sorry for not wording my question clearly. The more I think about it, it might be better to use a parameter and run each error code separately. Thanks.

Collectives™ on Stack Overflow

Dynamically adding columns to query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related