Stored procedure error handling best practise

Question

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

I don't know much about this (thus I do not know if it is the most efficient which it probably is not) but you could try to use try catch...msdn.microsoft.com/en-us/library/ms175976.aspx — CodyMR
– CodyMR, Commented Sep 9, 2016 at 13:58
From link it looks as if il need a try catch for each exec statement. — user2202098
– user2202098, Commented Sep 9, 2016 at 14:08
Actually, if you look a little further down from the top it tells you how to get the error. There is a way to see which procedure caused the error. So, in theory, you could have one big TRY CATCH over your procedures. Again, I have not used it before so this is me guessing. — CodyMR
– CodyMR, Commented Sep 9, 2016 at 14:12

Anuj Tripathi · Accepted Answer · 2016-09-09 14:30:48Z

1

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.

answered Sep 9, 2016 at 14:30

Anuj Tripathi

2,29114 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user2202098 Over a year ago

Thanks, much appreciated

TheGameiswar Over a year ago

Note all errors are not caught,since all sps are in same batch,failure in one sp can terminate whole batch

CodyMR Over a year ago

@TheGameiswar Is it possible to TRY CATCH all three EXEC and use the ERROR_PROCEDURE() to display which one had the error?

Anuj Tripathi Over a year ago

@Cody360c yes you can keep in that way also. You can use like BEGIN TRY -- your code PRINT 'SP1 EXECUTED SUCCESSFULL' END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; END CATCH

Anuj Tripathi Over a year ago

or alternatively, you can remove TRY-CATCH from SP1, SP2 and SP3 and put them in MAIN SP BEGIN TRY EXEC dbo.sp1 PRINT 'SP1 EXECUTED SUCCESSFULL' END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; END CATCH and so.. on....

|

Collectives™ on Stack Overflow

Stored procedure error handling best practise

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related