Can you detect INSERT-EXEC scenario's?

Is it possible to DETECT whether the current stored procedure is being called by an INSERT-EXEC statement?

Yes, I understand we may want to no longer use INSERT-EXEC statements...that is NOT the question I am asking.

The REASON I am using INSERT-EXEC is because i am hoping to promote re-use of stored procedures rather than re-writing the same SQL all the time.

Here's why I care:

Under the INSERT-EXEC scenario the original error message will get lost once a ROLLBACK is requested. As such, any records created will now be orphaned.

Example:

ALTER PROCEDURE [dbo].[spa_DoSomething]
(
    @SomeKey    INT,
    @CreatedBy  NVARCHAR(50)
)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION

        -- SQL runs and throws an error of some kind.

        COMMIT TRAN
    END TRY
    BEGIN CATCH

        IF @@TRANCOUNT > 0
       开发者_JAVA技巧     ROLLBACK TRAN

        -- If this procedure is called using an INSERT-EXEC
        -- then the original error will be lost at this point because
        -- "Cannot use the ROLLBACK statement within an INSERT-EXEC statement." 
        -- will come-up instead of the original error.

        SET @ErrorMessage = ERROR_MESSAGE();
        SET @ErrorSeverity =    ERROR_SEVERITY();
        SET @ErrorState =       ERROR_STATE();

        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
    END CATCH

    RETURN @@Error
END

I've come up with a bit of a kludge (ok, it's a big kludge), based on the fact that you can't have nested INSERT--EXECUTE... statements. Basically, if your "problem" procedure is the target of an INSERT--EXECUTE, and itself contains an INSERT--EXECUTE, then an error will be raised. To make this work, you'd have to have a (quite probably pointless) INSERT--EXECUTE call in the procedure, and wrap it in a TRY--CATCH block with appropriate handling. Awkward and obtuse, but if nothing else comes up it might be worth a try.

Use the following to test it out. This will create three procedures:

IF objectproperty(object_id('dbo.Foo1'), 'isProcedure') = 1
    DROP PROCEDURE dbo.Foo1
IF objectproperty(object_id('dbo.Foo2'), 'isProcedure') = 1
    DROP PROCEDURE dbo.Foo2
IF objectproperty(object_id('dbo.Foo3'), 'isProcedure') = 1
    DROP PROCEDURE dbo.Foo3

GO
--  Returns a simple data set
CREATE PROCEDURE Foo1

AS

    SET NOCOUNT on

    SELECT name
     from sys.databases

GO
--  Calls Foo1, loads data into a local temp table, then returns those contents
CREATE PROCEDURE Foo2

AS

    SET NOCOUNT on

    CREATE TABLE #Temp (DBName sysname not null)

    BEGIN TRY
        INSERT #Temp (DBName)
         EXECUTE Foo1
    END TRY

    BEGIN CATCH
        IF ERROR_NUMBER() = 8164
            PRINT 'Nested INSERT EXECUTE'
        ELSE
            PRINT 'Unanticipated err: ' + cast(ERROR_NUMBER() as varchar(10))
    END CATCH


    SELECT *
     from #Temp

GO
--  Calls Foo2, loads data into a local temp table, then returns those contents
CREATE PROCEDURE Foo3

AS

    SET NOCOUNT on

    CREATE TABLE #Temp2 (DBName sysname not null)

    INSERT #Temp2 (DBName)
     EXECUTE Foo2

    SELECT *
     from #Temp2
GO

EXECUTE Foo1 will return the "base" data set.

EXECUTE Foo2 will call Foo1, load the data into a temp table, and then return the contents of that table.

EXECUTE Foo3 attempts to do the same thing as Foo2, but it calls Foo2. This results in a nested INSERT--EXECUTE error, which is detected and handled by Foo2's TRY--CATCH.

Maybe @@NESTLEVEL can help:

http://msdn.microsoft.com/en-us/library/ms187371.aspx