Help with adding column.

[delphidestructor]

Can anyone tell me why the first works in a sp and the second doesn't?

1)

CREATE PROCEDURE w_z_testing AS
create table #t_table (f1 int, f2 int)
alter table #t_table add f3 int
insert into #t_table (f1, f2) values (2,3)
select * from #t_table
GO

RETURNS:


(1 row(s) affected)

f1 f2 f3
----------- ----------- -----------
2 3 NULL

////////////////////////////////////////////

2)

CREATE PROCEDURE w_z_testing AS
create table #t_table (f1 int, f2 int)
alter table #t_table add f3 int
insert into #t_table (f1, f2, f3) values (2,3,4) <--
select * from #t_table
GO


RETURNS:

Server: Msg 207, Level 16, State 1, Procedure w_z_testing, Line 4
Invalid column name 'f3'.



Mike

 

[SQLBill]

Try it with a GO after the ALTER TABLE line and before the INSERT line.

It might be an issue of when f3 exists...

-SQLBill

 

[delphidestructor]

Yes I did try the GO after the adding of the column. The temp table then goes out of scope and the insert call will not compile. Creating the table with ## then calling GO might work but I didn't want the scope of the table to be global.

Mike