存储过程嵌套使用技巧

第一步

create table a

( fid numeric(10),

fno varchar(10),

fname varchar(50)

)

 

insert into a

values(1,'1','1')

 

第二步

IF EXISTS (SELECT name FROM sysobjects WHERE  name = 'p_c' AND type = 'P')

    DROP PROCEDURE p_c

GO

CREATE PROCEDURE p_c

AS

       SELECT * from a

GO

EXECUTE p_c

GO

 

第三

IF EXISTS (SELECT name FROM sysobjects WHERE  name = 'p_b' AND type = 'P')

    DROP PROCEDURE p_b

GO

CREATE PROCEDURE p_b

AS

IF EXISTS(SELECT name FROM sysobjects WHERE  name = N'b' AND type = 'U')

    DROP TABLE b

       create table b

       (

       fid numeric(10),

       fno varchar(10),

       fname varchar(50)

       )

       insert into b exec p_c

       select * from b

GO

EXECUTE p_b

GO

 

第四

IF EXISTS (SELECT name FROM sysobjects WHERE  name = 'p_a' AND type = 'P')

    DROP PROCEDURE p_a

GO

CREATE PROCEDURE p_a

AS

       create table #c

       (

       fid numeric(10),

       fno varchar(10),

       fname varchar(50)

       )

       insert into #c exec p_b

       select * from #c

GO

EXECUTE p_a

GO

 

问题，在第三步的时候没有问题的，但到了第四步的时候却提示

  服务器: 消息 8164，级别 16，状态 1，过程 p_b，行 17

  INSERT EXEC 语句不能嵌套。

 （所影响的行数为 0 行）

 （所影响的行数为 0 行）

各位有什么高见！

 

Answer:

把第二层改为function

--第一

create table a

(fid numeric(10),

fno varchar(10),

fname varchar(50)

)

 

insert into a

values(1,'1','1')

 

--第二步

CREATE FUNCTION p_c ()

RETURNS @a TABLE

   (

fid numeric(10),

fno varchar(10),

fname varchar(50)

)

 

AS

BEGIN

   INSERT @a

        SELECT * FROM a

 RETURN

END

 

--第三

IF EXISTS (SELECT name FROM sysobjects WHERE  name = 'p_b' AND type = 'P')

    DROP PROCEDURE p_b

GO

CREATE PROCEDURE p_b

AS

 

set nocount on

 

IF EXISTS(SELECT name FROM sysobjects WHERE  name = N'b' AND type = 'U')

    DROP TABLE b

       create table b

       (

       fid numeric(10),

       fno varchar(10),

       fname varchar(50)

       )

       insert into b SELECT *  from p_c ()

       select * from b

GO

EXECUTE p_b

 

--第四

IF EXISTS (SELECT name FROM sysobjects WHERE  name = 'p_a' AND type = 'P')

    DROP PROCEDURE p_a

GO

CREATE PROCEDURE p_a

AS

set nocount on

 

       create table #c

       (

       fid numeric(10),

       fno varchar(10),

       fname varchar(50)

       )

       insert into #c exec p_b

       select * from #c

GO

EXECUTE p_a