Throw error on TRY, in order to manage all error instruction in the CATCH block

Ma3x 0
Viewed 16

I'm writing a Stored Procedure to do "lots of things" (input validation, insert and update statements, transaction and cursors management and so on). I'd like to use TRY/CATCH in order to manage

  • transaction rollback
  • cursor closing
  • error printing in a single code block (I don't like to repeat ROLLBACK TRANSACTION T1 every time i check something which may let the work be rolled back...).

For example:

BEGIN TRY
    BEGIN TRANSACTION T1
    SET @val1 = (
        SELECT val1
        FROM tab1
        WHERE cond1 = 'cond1'
    )

    IF (ISNULL(@val1, '') = '')
    BEGIN
        -- insert the throw of an error
    END
    -- other code...
    COMMIT TRANSACTION T1
END TRY
BEGIN CATCH
    -- error handling code, included the error thrown in the IF above
    ROLLBACK TRANSACTION T1
    RETURN
END CATCH

Is it possible to throw an error in the BEGIN/END of the IF statement to go into the BEGIN CATCH so that all errors are handled in a single block, even those raised explicitly by me?

try-catch error-handling stored-procedures sql-server
1 Answers

Try something like the below.

BEGIN TRY
    BEGIN TRANSACTION T1
    SELECT 1;
    SELECT 2;
    SELECT 3;
    RAISERROR('Here is my error!', 16, 1);
    SELECT 4;
    SELECT 5;

    COMMIT TRANSACTION T1
END TRY
BEGIN CATCH
    DECLARE @x VARCHAR(1000);
    SELECT @x = ERROR_MESsAGE();
    print @x;

    IF (XACT_STATE() <> 0) ROLLBACK TRAN;
--  RETURN
END CATCH
useroqMi0
Related