December 4, 2001 at 4:49 am
Hi,
Everybody say that temporary tables are just like normal tables.
But can we perform alter and update operations on temporary tables as we do
for normal tables?
I am facing a problem with temporary tables created in stored procedures in
SQL server 2000.
I have written a Stored procedure as follows:
----------------------------------------------
create procedure dbo.Alter_Temp_Table
as
begin
create table #dummy
(
PERSONAL_REFERENCE varchar(10)
)
insert #dummy ( PERSONAL_REFERENCE )
select PERSONAL_REFERENCE from StaffMembers
where CURRENT_RECORD=1
alter table #dummy add STAFF_NAME varchar(60) NULL
update d
set d.STAFF_NAME = sm.FIRST_NAME
from #dummy d, StaffMembers sm
where sm.PERSONAL_REFERENCE = d.PERSONAL_REFERENCE
select * from #dummy
drop table #dummy
end
go
-------------------------------------
When I compile the above stored proc, it compiles with no errors.
But when I execute it along with the update statement above, it throws an
error giving:
Invalid column name 'STAFF_NAME'
But if I comment the update statement in the above Stored Proc and execute,
it returns both the columns (including STAFF_NAME having NULL values in it).
Can anybody please give me a solution how to alter a temporary table and update
it in a stored procedure?
Thanks in advance.
Naveen M
Naveen M
December 4, 2001 at 5:02 am
If you use a go after the alter table it should work ok - eg:
create table #dummy
(
PERSONAL_REFERENCE varchar(10)
)
insert #dummy ( PERSONAL_REFERENCE )
values(1)
alter table #dummy add STAFF_NAME varchar(60) NULL
go
update #dummy
set #dummy.STAFF_NAME = 'xxx'
select * from #dummy
drop table #dummy
go
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
December 6, 2001 at 1:41 am
quote:
If you use a go after the alter table it should work ok - eg:create table #dummy
(
PERSONAL_REFERENCE varchar(10)
)
insert #dummy ( PERSONAL_REFERENCE )
values(1)
alter table #dummy add STAFF_NAME varchar(60) NULL
go
update #dummy
set #dummy.STAFF_NAME = 'xxx'
select * from #dummy
drop table #dummy
go
Paul Ibison
Hi Paul,
I tried this by giving GO after the alter table. Still I get the same error.
Any other solution please..
Thanks
Naveen
Naveen M
Naveen M
December 6, 2001 at 4:27 am
don't know if this will help, but create a global temp table by using ## instead of #??
don't forget to drop the table when finished
December 6, 2001 at 4:59 am
Global temp is not a bad idea, but easy to get mixed up if you have concurrent users. Honestly, what is the point in this case of modifying a table you just created, why would you not create it the way you needed to start with?
Anyway, take a look at this article:
http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q295305
Andy
December 6, 2001 at 5:57 am
can't include a go in a stored procedure.
If you call a another SP after the alter to access the table then this will be called at run time with the altered table definition and should work.
Cursors never.
DTS - only when needed and never to control.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply