Tag: Exception Handling

  • Exception Handling using Try-Catch in SQL Server 2005

    /*
    Error Handling using Try Catch Block
    */

    CREATE PROCEDURE usp_GetErrorInfo
    AS
    BEGIN
    SET NOCOUNT ON
    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
    GO

    /*
    TRY – CATCH in user defined stored procedure
    */

    ALTER PROCEDURE TEST
    AS
    BEGIN
    BEGIN TRY
    — Generate divide-by-zero error.
    SELECT 1/0;
    END TRY
    BEGIN CATCH
    — Execute error retrieval routine.
    EXECUTE usp_GetErrorInfo;
    END CATCH
    END
    GO

    EXECUTE TEST
    GO;