5

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
  • 1
    When ran inside a stored procedure that code shouldn't throw that error. The table doesn't exist at first so all referencing statements should be subject to deferred compile. Please provide a full example that reproduces the issue. – Martin Smith Dec 9 '14 at 6:09
  • Except if you have a temp table with the same name in a different scope (higher up the call stack) you might get this I suppose. Or maybe if you are on an ancient version such as 2000. – Martin Smith Dec 9 '14 at 21:27
  • @MartinSmith see below for correct answer. – JohnOsborne Dec 10 '14 at 23:40
  • Correct answer to what? You still haven't provided an example of this happening. I copy and paste the code that you have provided into a SQL Fiddle and it works fine. sqlfiddle.com/#!6/80711/1 – Martin Smith Dec 10 '14 at 23:45
  • The only way I can see this happening at execution time is if you are creating a temp table of the same name outside the stored procedure. Example sqlfiddle.com/#!6/e937d/1 – Martin Smith Dec 11 '14 at 0:05
8

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.

  • Aaron, I just had to google Estimated Plan but I just copied and pasted what I felt was necessary. The way I saw it. The person reading my question could see that a table existed, and it was altered. I didn't feel any necessary information was needed. Looking back, I could of shown that the table was originally loaded with dynamic SQL as well but I didn't feel that was relevant to the question. In Summary, I didn't want to bog down readers with too much info. Am I totally off here? Any constructive criticism IS GREATLY APPRECIATED. Thank you. – JohnOsborne Dec 10 '14 at 23:36
  • I improved the title to get rid of vagueness – JohnOsborne Dec 10 '14 at 23:39
  • 1
    @CodeFlava Do not, ever, err on the side of providing less information than you have. We're pretty smart people and are not going to get bogged down by differences like that. We want to know exactly what is happening, not something that is kind of similar to what is sort of happening. So, while you've been asked multiple times by multiple people, I'll ask once more: can you update the question (not the title!) with the exact code that is causing the error, and the full error message? – Aaron Bertrand Dec 10 '14 at 23:56
  • Aaron, the exact code that is causing the error was and is there. – JohnOsborne Dec 11 '14 at 1:44
  • You wrote that this "shouldn't happen during normal execution" but can it happen during normal execution, even if I'm not using 'Display estimated execution plan' in SSMS? I'm getting the same error even when running my stored procedure via ADO.NET code. I think I ran into the problem covered by this Stack Overflow question about temp tables with the same name created in nested stored procedures. – Kenny Evitt Jan 5 '16 at 16:39
1

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?

  • See correct answer. – JohnOsborne Dec 9 '14 at 23:26
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.