referring to added temp table columns in same sp

Question

Post reply

referring to added temp table columns in same sp

jason_langston

Mr or Mrs. 500

Points: 553
More actions
November 6, 2002 at 9:09 am

#158526

When I add a column to a temp table in a sproc, I can't later reference that column in the same sproc. Is this really a feature/limitation of T-SQL/sprocs or am I missing something?
Here's some sample code:
CREATE PROC usp_test
AS
select *
into #test_temp
from authors;
alter table #test_temp add mycolumn varchar(50) null;
update #test_temp set mycolumn = 'hello';--line 10
select * from #test_temp;
GO
-- run it
usp_test
/* results
(23 row(s) affected)
Server: Msg 207, Level 16, State 1, Procedure usp_test, Line 10
Invalid column name 'mycolumn'.
*/
I've just learned to work around this type of problem by creating two sp's and calling them both from one "wrapper" sp. That seems like a "hack". The above procedure just seems like it should work (esp. since it will work when the sproc body is run as a script)
Any thoughts? tips?
TIA,
JasonL

JasonL

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

paulhumphris Hall of Fame Points: 3496 More actions · Answer 1

Have you tried creating your temp table before you populate it.

CREATE TABLE #test_temp.......

INSERT INTO #test_temp (....)

SELECT .....

FROM authors

ALTER TABLE #test_temp ADD mycolumn varchar(50) null

etc etc....

jason_langston Mr or Mrs. 500 Points: 553 More actions · Answer 2

Just to followup, the following code works:

CREATE PROC usp_test

AS

select *,'x' as mycolumn

into #test_temp

from authors;

alter table #test_temp alter column mycolumn varchar(50) null;

update #test_temp set mycolumn = 'hello';--line 10

select * from #test_temp;

This gives me the hoped for results, and allows me to control the data type of the additional column.

I guess I'm just looking for a little more detail on deferred name resolution/sproc compilation so I understand *why* the first example I posted doesn't work.

paul: yeah, I've tried that. It doesn't help the sproc chokes whenever it accesses a column added after the table creation.

Thanks,

JasonL

phumphris Newbie Points: 1 More actions · Answer 3

Have you taken a look at this article

http://www.sqlservercentral.com/columnists/awarren/missingtemptablesfollowup.asp

I the example given is experiencing something similar.

jason_langston Mr or Mrs. 500 Points: 553 More actions · Answer 4

paul: thanks for the link ... but I don't think it's that.

Andy's problem was undoubtedly related to something in the ADO/OLE DB layers he was using.

The problem I described is not intermittent - it happens every time. I'm sure the problem is due to a limitation (feature?) in SQL Server's deferred resolution.

This KB article http://support.microsoft.com/default.aspx?scid=kb;en-us;Q295305 clearly shows that the problem I described is by design. You have to use a nested sproc.

If anyone has tips/info on the details of deferred resolution to explain why, I'd love to hear it!

Thanks,

JasonL

jason_langston Mr or Mrs. 500 Points: 553 More actions · Answer 5

Finally, to answer my question one last time!

In the MSDN Online library this note is given on the subject of deferred name resolution:

quote:
Note Deferred Name Resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table.

This explanation is **much** clearer than BOL, and is exactly what I was looking for.

JasonL