Tag Archives: 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;