Working with Try Catch

Each Try..Catch construct must be inside a single batch, stored procedure or trigger.

BEGIN TRY
    SELECT *
        FROM sys.messages
        WHERE message_id = 21;
END TRY
GO
-- The previous GO breaks the script into two batches,
-- generating syntax errors. The script runs if this GO
-- is removed.
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
GO

  • Errors that have a severity of 20 or higher that cause the Database Engine to close the connection will not be handled by the TRY…CATCH block. However, TRY…CATCH will handle errors with a severity of 20 or higher as long as the connection is not closed.

  • Errors that have a severity of 10 or lower are considered warnings or informational messages, and are not handled by TRY…CATCH blocks.

    Error Functions

    TRY…CATCH uses the following error functions to capture error information:

    • ERROR_NUMBER() returns the error number.

    • ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters such as lengths, object names, or times.

    • ERROR_SEVERITY() returns the error severity.

    • ERROR_STATE() returns the error state number.

    • ERROR_LINE() returns the line number inside the routine that caused the error.

    • ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.

    TEST 1
    -- CHECK IF THIS PROCEDURE EXISTS
    IF OBJECT_ID('USP_MSSQL_TRY_CATCH_ERRORS','P') IS NOT NULL
    DROP PROCEDURE USP_MSSQL_TRY_CATCH_ERRORS
    GO

  • -- CREATE A TEST PROCEDURE
    CREATE PROCEDURE USP_MSSQL_TRY_CATCH_ERRORS
      @Divisor FLOAT
     ,@Dividend FLOAT
     ,@Result FLOAT OUTPUT
    AS
     BEGIN TRY
      SET @Result = @Divisor/@Dividend
     END TRY
     BEGIN CATCH
      SELECT
         ERROR_LINE()   AS ErrorLine   -- RETURN INT
       , ERROR_MESSAGE ()  AS ErrorMessage   -- RETURN NVARCHAR(1)
       , ERROR_NUMBER ()  AS ErrorNumber   -- RETURN INT
       , ERROR_PROCEDURE () AS ProcedureName  -- RETURN NVARCHAR(1)
       , ERROR_SEVERITY ()  AS ErrorSeverity  -- RETURN INT
       , ERROR_STATE ()  AS ErrorState   -- RETURN INT
     END CATCH
    GO

    -- CALL PROCEDURE WITHOUT ILLEGAL PARAMETERS
    DECLARE @RES  FLOAT
    EXEC USP_MSSQL_TRY_CATCH_ERRORS 10,2,@RESULT
    =@RES OUTPUT
    SELECT (@RES) AS RESULT
    GO

    -- CALL PROCEDURE WITH AN ILLEGAL PARAMETERS
    DECLARE @RES FLOAT
    EXEC USP_MSSQL_TRY_CATCH_ERRORS 10,0,@RESULT = @RES OUTPUT
    SELECT (@RES) AS RESULT
    GO

  • 創作者介紹
    創作者 shadow 的頭像
    shadow

    資訊園

    shadow 發表在 痞客邦 留言(0) 人氣()