Checking if column exists in temporary table always returns false in SQL Server

Question

I have the following Execution statement which creates a table (using data from another procedure), inserts the values into a temporary table, adds an image column (because they cannot be included in the grouping), and then updates it based on criteria from another temporary table (the resulting table fields gets used in a SSRS report, so I need to retain the IMAGE data type):

EXEC ('SELECT ' + @COL_TO_GROUP_BY + @COL_ADDITONAL + @sColVals + ' INTO
#RESULTS_TABLE from (' + @SQL_STATEMENT + ') A ' + @WHERE_CLAUSE + ' GROUP BY ' +
@COL_TO_GROUP_BY +

' ALTER TABLE #RESULTS_TABLE
ADD IMAGE_DATA IMAGE

IF EXISTS(SELECT * FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 
''COLUMN_A'' and TABLE_NAME LIKE ''#RESULTS_TABLE%'')
BEGIN
    UPDATE #RESULTS_TABLE
    SET IMAGE_DATA = FILE_DATA
    FROM #RESULTS_TABLE A, #IMAGE_TABLE B 
    WHERE A.COLUMN_A = B.COLUMN_A
END

SELECT * FROM #RESULTS_TABLE')

The problem is that regardless of whether COLUMN_A exists or not, the column for IMAGE_DATA is always NULL. Is there any other way to get data into the IMAGE_DATA column? Please help!

Note: I will not be accepting any answers that conclude that the problem is related to the content in other tables, more specifically from the WHERE clause. I have done multiple validations to ensure the conditions can be both true and false (matching rows, no matching rows, etc..). So this rules out the conditional statement. Thanks.

EDIT:

I'm still not completely sure what the exact cause of it was, but I ended up creating a global temporary table and ran two separate procedures, now it seems to be working fine. I had to accept the answer that most closely matched my own solution. All answers and comments were very viable. Thanks!

Solution 1

Correct.

At the time of compilation, the column doesn't exist. That is, SQL Server looks at the whole set of commands and parses/compiles it. The effects of an ALTER TABLE in the code won't be visible to later commands.

You have to do the ALTER TABLE separately to the UPDATE

Note: For SQL Server 2005 you'd have varbinary(max) which is far more flexible and remove some complexity

Solution 2

You have several problems in your script:

• TempDB is not the correct name of tempdb. Your code will break on a server installed with a case sensitive collation. Always use the proper case for all database names. You code is likely to break similarly on the result column names, like COLUMN_A, if run under a case sensitive deployment.
• The logic is incorrect under concurrency: session A will see the #temp table of session B and do incorrect actions.
• A simple test shows that the columns are visible:

.

EXEC ('SELECT * INTO #RESULTS_TABLE from 
     (select *, newid() as COLUMN_A from master..spt_values) A;
ALTER TABLE #RESULTS_TABLE
ADD IMAGE_DATA varbinary(max);
IF EXISTS(SELECT * FROM TempDB.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 
''COLUMN_A'' and TABLE_NAME LIKE ''#RESULTS_TABLE%'')
BEGIN
    update #RESULTS_TABLE 
       set IMAGE_DATA = 0xBAADF00D;
END
SELECT * FROM #RESULTS_TABLE')

This test shows that the column was updated, so the check for EXISTS succeeded. The obvious conclusion is that in your case the OBJ_ID join between #RESULTS_TABLE and #IMAGE_TABLE does not find any match, which is a problem that depends entirely on the content of your tables.

Edit

You can make the COLUMN_Ato be dynamic, it still works fine when tested:

declare @cn sysname = case 
      when RAND() * 100 >= 50 then 'COLUMN_A' 
      else 'COLUMN_B' end;

EXEC (N'SELECT * INTO #RESULTS_TABLE from (
     select *, newid() as ' + @cn + N'  from master..spt_values) A;
...

Solution 3

You can Check if column exists in temporary table by using..

IF EXISTS (SELECT * FROM TempDB.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'Column' AND TABLE_NAME LIKE '#TempTable%')
    PRINT 'Column exists'
ELSE
    PRINT 'Column doesn''t exists'

Solution 4

I know this is a very old thread but wanted to share my solution.

The problem is that TABLE_NAME returned by the INFORMATION_SCHEMA.COLUMNS view is the table name as it is stored in the tempdb.sys.objects table, which includes an indicator of session.

You cannot use "TABLE_NAME like 'RESULTS_TABLE%'" because it is not session safe.

The solution is to query the schema directly with the object id of the table instead of using the INFORMATION_SCHEMA views as follows:

if exists (Select 1 
           from tempdb.sys.columns 
           where [object_id] = object_id('tempdb..#RESULTS_TABLE') 
             and name ='COLUMN_A')

View more solutions

Comments

[Carmen W]

Thanks for your reply. I am using SQL Server 2008. I tried converting the image column to varbinary(max), and even to varbinary from binary (per other suggestions) but it gives me gibberish, so that was not an option. Can you please clarify how I should do an ALTER TABLE separately from the UPDATE? Performing two EXEC statements is out of scope for the temporary table. Thanks.

[Carmen W]

Thanks for your answer. Please see the edits to the question. Bare with me as this is my first question using this forum, so I should have made the question more clear the first time. I will try using the procedure you used for your test.

[Remus Rusanu]

No offense but whenever I'm faced with 'your word vs. tested behavior' I pick 'tested behavior'. The ball is in your court, you need to prove the IF branch is not taken.

[Carmen W]

I looked at your script and realized there's a difference in the way you have executed it vs the way I have. My script is not explicitly creating COLUMN_A. COLUMN_A shows up depending on the content pulled from another procedure (another table) and is therefore dynamic. You're right, if I do it this way it works fine (and I probably wouldn't need to do the check at all in this case). I think the problem is with dynamically created columns.

[Remus Rusanu]

The test still works fine if COLUMN_A is dynamically generated, see my update.

[Carmen W]

I ended up creating a global temporary table and two separate procedures. It seems to be working now so I won't meddle much more with it. :) I had to accept the answer from gbn because his reasoning matched most closely with my solution. However, many thanks for your efforts.

[Tone Škoda]

This is not ok if 2 databases on server use same tmp table names. This script will see tmp table on other db and will think it exists in current db.