[sql-server] 오류 : “INSERT EXEC 문은 중첩 될 수 없습니다.” 및 “INSERT-EXEC 문 내에서 ROLLBACK 문을 사용할 수 없습니다.” 이것을 해결하는 방법?

좋습니다. jimhark의 권장 사항은 이전 단일 해시 테이블 접근 방식의 예입니다.-

CREATE PROCEDURE SP3 as

BEGIN

    SELECT 1, 'Data1'
    UNION ALL
    SELECT 2, 'Data2'

END
go


CREATE PROCEDURE SP2 as

BEGIN

    if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#tmp1'))
        INSERT INTO #tmp1
        EXEC SP3
    else
        EXEC SP3

END
go

CREATE PROCEDURE SP1 as

BEGIN

    EXEC SP2

END
GO


/*
--I want some data back from SP3

-- Just run the SP1

EXEC SP1
*/


/*
--I want some data back from SP3 into a table to do something useful
--Try run this - get an error - can't nest Execs

if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#tmp1'))
    DROP TABLE #tmp1

CREATE TABLE #tmp1 (ID INT, Data VARCHAR(20))

INSERT INTO #tmp1
EXEC SP1


*/

/*
--I want some data back from SP3 into a table to do something useful
--However, if we run this single hash temp table it is in scope anyway so
--no need for the exec insert

if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#tmp1'))
    DROP TABLE #tmp1

CREATE TABLE #tmp1 (ID INT, Data VARCHAR(20))

EXEC SP1

SELECT * FROM #tmp1

*/

답변

insert into @myTempTable
exec THISSERVER.MY_DATABASE_NAME.MY_SCHEMA.SP2