Procedure Run Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Procedure Run Error

Author

Topic

panwang
Starting Member

2 Posts

Posted - 2003-10-20 : 20:47:54

Create PROCEDURE TEST1
AS

CREATE TABLE #t
(
LsID int NULL,
vNo varchar(60) NULL,
vName varchar(60) NULL,
dPrice decimal(10,2) NULL
)

insert into #t(LsID,vNo,vName,dPrice) values(22,'001','235',232.22)

select * from #t

ALTER TABLE #t DROP COLUMN LsID

select * from #t

drop table #t

go

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-20 : 20:50:03

see
http://www.nigelrivett.net/AccessTempTablesAcrossSPs.html

The SP resolves the object structure when it compiles. You are then changing it so it gets confused.
You can access the changed structure from another SP or dynamic sql.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

panwang
Starting Member

2 Posts

Posted - 2003-10-20 : 20:59:07

it can not help me.

please notice drop column after insert data to temp table

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-20 : 21:39:19

insert into #t(LsID,vNo,vName,dPrice) values(22,'001','235',232.22)

select * from #t

ALTER TABLE #t DROP COLUMN LsID

exec ('select * from #t')

drop table #t

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources