SQL Server 2008 : Error handling in SP

57 Views Asked by At

I have a stored procedure that has 2 flaws:

  1. If there is an error in the stored procedure, it keeps executing. I want the stored procedure to stop immediately once there is any error found.

  2. There is an error log table for this stored procedure, I have customized the error and if the error do happen, it repeats the same error message multiple times because of the while loop used in the stored procedure. I want the error to be logged only once per occurrence.

This is the general outline of the stored procedure code:

ALTER PROCEDURE [sp_abc]
AS 
BEGIN
    create table #abc1 (id int, name char(20), done bit default 0)
    values (1,'ben'), (2,'charles')

    WHILE (SELECT count(*) from #abc1 where done = 0) > 0 
    BEGIN
       BEGIN TRY 
       BEGIN TRANSACTION
           INSERT INTO abc123 (col1, col2)
           VALUES (1,2), (3,4)
    END
    COMMIT TRANSACTION
        END TRY
        BEGIN CATCH
            DECLARE @error_number INT
            ,       @error_message VARCHAR(MAX)

            SET @error_number = ERROR_NUMBER()
            SET @error_message = ERROR_MESSAGE() + '  [Trying to insert data into abc123]'

            ROLLBACK TRANSACTION

            -- Insert error into an error table
            INSERT INTO [dbo].[error_log] ( sp_name,[error_message] )
            SELECT DISTINCT ' sp123 ', @error_message 
        END CATCH
END
0

There are 0 best solutions below