SQL SERVER INSERT EXEC嵌套问题解决方案

INSERT EXEC嵌套问题

 

--第一个存储过程_TestA

ALTER PROCEDURE[dbo].[_TestA] @SNNVARCHAR(50)= NULL

AS

    SET NOCOUNT ON;

    BEGIN

 

       CREATE TABLE#Detail

           (

             sn VARCHAR(50),

             oem_sn VARCHAR(50),

             Product_Name VARCHAR(200)

           );

 

 

       INSERT  INTO#Detail

               SELECT  @SN ,

                       '' ,

                       '';

 

       SELECT  *

       FROM    #Detail;

 

    END;

 

--第二个存储过程_TestB

ALTER PROCEDURE[dbo].[_TestB]

AS

    BEGIN

       SET NOCOUNT ON;

 

       CREATE  TABLE#Detail1

           (

             sn VARCHAR(50),

             oem_sn VARCHAR(50),

             Product_Name VARCHAR(200)

           );

 

 

       INSERT  INTO#Detail1

       EXEC [dbo].[_TestA]@SN= '021VQR2SEA053122';

   

 

       SELECT  *

       FROM    #Detail1;

 

    END;

 

系统很多   这样的_TestB 嵌套调用_TestA的数据，都没有问题.

 

但是随着系统扩展，出现了第二次嵌套

--第三个存储过程_TestC

ALTER PROCEDURE[dbo].[_TestC]

AS

    SET NOCOUNT ON;

    BEGIN

       CREATE TABLE#Detail2

           (

             sn VARCHAR(50),

             oem_sn VARCHAR(50),

             Product_Name VARCHAR(200)

           );

 

       INSERT  INTO#Detail2

       EXEC [dbo].[_TestB];

 

       SELECT  *

       FROM    #Detail2;

 

    END;

 

这种状况下，就会报错

Msg 8164, Level 16, State1, Procedure _TestB, Line 30

An INSERT EXEC statementcannot be nested.

 

 

 

 

解决方案:

 

http://stackoverflow.com/questions/3795263/errors-insert-exec-statement-cannot-be-nested-and-cannot-use-the-rollback-s

http://www.sommarskog.se/share_data.html

 

简单来说就是把第一层_TestA改成表值函数

--select * from[dbo].[UFT_TestA]('021VQR2SEA053122')

 

 

ALTER FUNCTION [dbo].[UFT_TestA]

(

 @SN NVARCHAR(50)= NULL

)

RETURNS

@Detail TABLE

            (

              sn VARCHAR(50),

              oem_sn VARCHAR(50),

              Product_Name VARCHAR(200)

            )

AS

BEGIN

      

                INSERT  INTO @Detail

                SELECT  @SN ,

                        '' ,

                        '';

                 --可进行一些业务操作

                update @Detail

                set oem_sn='222'

                where sn=@SN

   

 

    RETURN

END

 

 

--第二个存储过程_TestB 引用函数

ALTER PROCEDURE[dbo].[_TestB]

AS

    BEGIN

       SET NOCOUNT ON;

 

       CREATE  TABLE#Detail1

           (

             sn VARCHAR(50),

             oem_sn VARCHAR(50),

             Product_Name VARCHAR(200)

           );

 

 

       INSERT  INTO#Detail1

       select * from [dbo].[UFT_TestA]('021VQR2SEA053122')

;

   

 

       SELECT  *

       FROM    #Detail1;

 

    END;

 

 

--第三个存储过程_TestC不变

ALTER PROCEDURE[dbo].[_TestC]

AS

    SET NOCOUNT ON;

    BEGIN

       CREATE TABLE#Detail2

           (

             sn VARCHAR(50),

             oem_sn VARCHAR(50),

             Product_Name VARCHAR(200)

           );

 

       INSERT  INTO#Detail2

       EXEC [dbo].[_TestB];

 

       SELECT  *

       FROM    #Detail2;

 

    END;