none
Stored procedure with try catch RRS feed

  • Question

  • I am trying to keep all my code in the try block of a stored procedure and errors in the catch block.

    I also want to store the time at which the sp started and each block in the sp started into a table directly when the code executes.

    Table has 4columns

    Table:

    ID                           SP_StartTime       FirstBlockStartTime             2Block_StartTime               Error

    IDENTITY(1,1)       SMALLDATETIME     SMALLDATETIME                 SMALLDATETIME              VARCHAR(MAX)

    CREATE PROC NAME

    AS

    BEGIN TRY

    INSERT TIME INTO Table(SP_StartTime) runtime

    First Block Code

    INSERT TIME INTO Table(FirstBlockStartTime) runtime

    2 Block Code

    INSERT TIME INTO Table(2Block_StartTime) runtime

    3 Block Code

    INSERT TIME INTO Table(3Block_StartTime) runtime

    END TRY

    BEGIN CATCH

    DECLARE @err VARCHAR(100)

    UPDATE Table

    SET err = @err

    WHERE ID = @ID

    END CATCH

    I don't want to use table variable.



    • Edited by naveej Wednesday, September 17, 2014 9:49 PM
    Wednesday, September 17, 2014 9:48 PM

Answers

  • Cool.

    Use THROW.

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

    CREATE PROCEDURE SP_TEST

    AS

    BEGIN

    BEGIN TRY DECLARE @BLOCK INT; SET @BLOCK = 1; PRINT 'DO THINGS HERE AND VALIDADE LATER.' IF GETDATE() > '2014-02-01' -- THIS CONSTITUTES AN ERROR SITUATION BEGIN PRINT 'ERROR'; THROW 51000, 'its past 1st of february.', 1; END SET @BLOCK = 2; PRINT 'MORE CODE HERE, BUT IT WONT RUN BECAUSE OF THE EXCEPTION ABOVE.' END TRY BEGIN CATCH PRINT 'THERE WAS AN ERROR: ' + ERROR_MESSAGE(); INSERT INTO TB_ERROR_CONTROL VALUES (@BLOCK, ERROR_MESSAGE(), GETDATE()); END CATCH

    END



    Wednesday, September 17, 2014 10:17 PM

All replies