CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
dilip
dilip

Reputation: 11

Stored procedure: Ensure that the second and subsequent procedures execute

I have a SQL Server stored procedure that executes several other stored procedures using exec <SP Name>.

How can I ensure that the second and subsequent procedures execute even if the first one fails?

Upvotes: 1

Views: 244

Answers (1)

LukeH
LukeH

Reputation: 269398

Use a TRY...CATCH block (assuming that you're running SQL Server 2005 or newer).

DECLARE @failed BIT
SET @failed = 0

BEGIN TRY
    EXEC your_first_sproc
END TRY
BEGIN CATCH
    SET @failed = 1
END CATCH

IF (@failed = 0)
BEGIN
    BEGIN TRY
        EXEC your_second_sproc
    END TRY
    BEGIN CATCH
        SET @failed = 1
    END CATCH
END

IF (@failed = 0)
BEGIN
    BEGIN TRY
        EXEC your_third_sproc
    END TRY
    BEGIN CATCH
        SET @failed = 1
    END CATCH
END

-- etc etc

Upvotes: 4

Related Questions