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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-02-22 : 09:03:54
|
Jon writes "I use SQL Server 7.0, Windows 2000
The following code, which works, is similar to something I have implemented:
create proc spOuter as
create table #foo (ColA int)
exec spInner1
exec spInner2
insert into #foo values (3,4)
select * from #foo
go
create proc spInner1 as
alter table #foo add ColB int
go
create proc spInner2 as
insert into #foo values (1,2)
go
If I change the nesting and procs slightly to below, however, the code fails in spInner1 before executing anything (even the print statement):
create proc spOuter as
create table #foo (ColA int)
exec spInner1
select * from #foo
go
create proc spInner1 as
print "In spInner1"
exec spInner2
insert into #foo values (1,2)
go
create proc spInner2 as
alter table #foo add ColB int
go
Why does the first example work but not the second? In addition, if an insert statement is added to spInner1 in the first example (after the alter stmt), it too will fail. I can't find any reference in the doc as to why this would be." |
|
|
|
|
|
Altering Temp Tables in Nested Stored Procedures