CODEWITHSUNDEEP
sqlsql-serverstored-procedures
user2202098
user2202098

Reputation: 860

Stored procedure error handling best practise

I have a stored procedure that calls into multiple other stored procedures. If the stored procedure hits an error, does it exit or continue to execute rest of code?

How can I ensure that if one section fails it prints the failed stored procedure and will continue to execute the next one.

E.g 

"spInsert_1 fail"
"spInsert_2 sucess"

Code:

CREATE PROCEDURE [dbo].[spInsertAll] 
AS
BEGIN
    SET NOCOUNT ON;

    exec dbo.spInsert_1
    exec dbo.spInsert_2
    exec dbo.spInsert_3
END

Upvotes: 1

Views: 339

Answers (1)

Anuj Tripathi
Anuj Tripathi

Reputation: 2281

Technically SQL Server will do that for you by default - However you may want to do the in more elegant way. Here is the way

Stored procedure 1

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp1]
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
    SELECT 1 
    PRINT 'SP1 is completed'
    END TRY
    BEGIN CATCH
    PRINT 'SP 1 is Failed'
    END CATCH'
END

Stored procedure 2 (which will generate the error)

CREATE PROCEDURE [dbo].[sp2]
AS
BEGIN
    SET NOCOUNT ON;
BEGIN TRY
SELECT 1/0 
PRINT 'SP2 is completed'
END TRY
BEGIN CATCH
PRINT 'SP 2 is Failed'
END CATCH
END

Stored procedure 3

CREATE PROCEDURE [dbo].[sp3]
AS
BEGIN

    SET NOCOUNT ON;
    BEGIN TRY
    SELECT 1 
    PRINT 'SP 3 is completed'
    END TRY
    BEGIN CATCH
    PRINT 'SP 3 is Failed'
    END CATCH
END

Main stored procedure

CREATE PROCEDURE dbo.Mainsp
AS
BEGIN

    SET NOCOUNT ON;

    EXEC dbo.sp1 
    EXEC dbo.sp2 
    EXEC dbo.sp3
END
GO

Message when you will execute main procedure

 SP 1 is completed 
 SP 2 is Failed 
 SP 3 is completed

Even if you don't use TRY-CATCH then also SQL will do the execution of stored procedure in the desired way. The only difference you will see is the success/failure message.

Upvotes: 1

Related Questions