June 9, 2011 at 5:02 am
Why insert from stored procedure works well, and start to have error when I've try to do it from over procedure?
It's hard for me to formulate the question, better look at the code:
create procedure test_0 as
declare @t table (ID int)
insert into @t values (1),(2),(3)
select ID from @t
go
create procedure test_1 as
declare @t1 table (ID int)
insert into @t1
exec test_0
select ID from @t1
go
declare @t2 table (ID int)
insert into @t2 -- this works
exec test_0
exec test_1 -- this works
insert into @t2 -- here have error : Msg 8164, Level 16, State 1, Procedure test_1, Line 4 La instrucción INSERT EXEC no se puede anidar.
exec test_1
select ID from @t2
go
drop procedure test_0,test_1
go
if you use temporary tables the result is the same, as with table variables.
June 9, 2011 at 6:36 am
June 9, 2011 at 7:23 am
Find the solution. Can do the same using functions:
CREATE FUNCTION test_0 ()
RETURNS @t table (ID int)
as
begin
insert into @t values (1),(2),(3)
RETURN
end
go
CREATE FUNCTION test_1 ()
RETURNS @t1 table (ID int)
as
begin
insert into @t1 (ID)
(select ID from test_0())
RETURN
end
go
declare @t2 table (ID int)
insert into @t2 (ID)
(select ID from test_0())
insert into @t2 (ID)
(select ID from test_1())
select ID from @t2
drop function test_1,test_0
go
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply