referring to added temp table columns in same sp

Question

Post reply

referring to added temp table columns in same sp

jason_langston

SSC-Addicted

Points: 413
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

paulhumphris

Hall of Fame

Points: 3496

November 6, 2002 at 10:37 am

#440513

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 SSC-Addicted Points: 413 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

phumphris

Newbie

Points: 1

November 6, 2002 at 12:14 pm

#440529

Have you taken a look at this article

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

I the example given is experiencing something similar.

jason_langston SSC-Addicted Points: 413 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 SSC-Addicted Points: 413 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