Update statement is giving an invalid column name error, even though the column exists

Question

I have a temporary table created at the beginning of this stored procedure. It is created successfully and can be selected from and inserted to. Here is the create statement

CREATE TABLE #tmpImportData 
( GuideFirstName VARCHAR(MAX), 
  GuideLastName VARCHAR(MAX), 
  email VARCHAR(MAX), 
  group_id_text VARCHAR(MAX), 
  CandidateName VARCHAR(MAX), 
  grade_text VARCHAR(5), 
  dateofbirth DATE
)

My problem is trying to update a column after I alter the temporary table. I get the error:

Msg 207, Level 16, State 1
invalid column name

Code:

declare @SQl1 nvarchar(max)
set @SQL1 ='
ALTER TABLE #tmpImportData 
ADD group_id INT
ALTER TABLE #tmpImportData 
ADD guide_id INT
ALTER TABLE #tmpImportData 
ADD password_plain_text VARCHAR(500)
ALTER TABLE #tmpImportData 
ADD guide_email VARCHAR(500)
ALTER TABLE #tmpImportData 
ADD class_id INT'

exec sp_executesql @Sql1

UPDATE #tmpImportData 
SET group_id = CAST(group_id_text AS INT)

UPDATE #tmpImportData 
SET group_id = 0 WHERE group_id IS NULL

Solution: NOT THE SOLUTION ANYMORE, IT DOESN'T WORK ANYMORE

declare @SQl1 nvarchar(max)
set @SQL1 ='
ALTER TABLE #tmpImportData ADD group_id INT
ALTER TABLE #tmpImportData ADD guide_id INT
ALTER TABLE #tmpImportData ADD password_plain_text VARCHAR(500)
ALTER TABLE #tmpImportData ADD guide_email VARCHAR(500)
ALTER TABLE #tmpImportData ADD class_id INT'

exec sp_executesql @Sql1
WAITFOR DELAY '000:00:05'

UPDATE #tmpImportData SET group_id = CAST(group_id_text AS INT)
UPDATE #tmpImportData SET group_id = 0 WHERE group_id IS NULL

What I thought would be better but still throws the invalid column name error Msg 207, Level 16, State 1, Line 2 Invalid column name 'group_id'.:

  declare @SQl1 nvarchar(max)
set @SQL1 ='
ALTER TABLE #tmpImportData ADD group_id INT, guide_id INT, password_plain_text VARCHAR(500), guide_email VARCHAR(500), class_id INT; UPDATE #tmpImportData SET group_id = CAST(group_id_text AS INT);
UPDATE #tmpImportData SET group_id = 0 WHERE group_id IS NULL; '

exec sp_executesql @Sql1
WAITFOR DELAY '000:00:05'

--UPDATE #tmpImportData SET group_id = CAST(group_id_text AS INT)
--UPDATE #tmpImportData SET group_id = 0 WHERE group_id IS NULL

SELECT * FROM #tmpImportData

Answer 1

You need to perform the update in dynamic SQL too (or just create the table with all columns in the first place). The error is happening because the update with the new column is being parsed before the dynamic SQL has run.

As an aside, you are probably only getting this error if you try to execute the stored procedure and choose 'Display estimated execution plan' in the toolbar. This is a parsing/binding error that will happen in many scenarios when generating an estimated plan involving #temp tables, but shouldn't happen during normal execution.

So to avoid the error during retrieval of an estimated plan, put any "new" column references in separate dynamic SQL blocks. But to really avoid the problem: stop retrieving estimated execution plans. They're pretty worthless anyway.

Answer 2

I am not able to reproduce the issue with the code you have given. Am getting only one warning message in the line

UPDATE #tmpImportData 
SET group_id = CAST(group_id_text AS INT)

like invalid column name group_id(its because the column is added to the temp table dynamically)

Can you recheck whether you are getting the issue with code you have given in the post?

Answer 3

I had similar problem Invalid Column Name pid but my procedure didn't even contain a table field named pid. The problem was I used same temporary table name in other stored procedures which were not properly cleared, so I changed the name of current temporary table to something different which I have never used and it worked fine.

So change the name of your temporary table to something you have never used in the past.