Exception Handling in SQL Server Stored Procedure with TRY CATCH Block in SqlServer

Introduction:

In this Example wel explain that  how to handle Exceptions in SQL Server or Exception Handling in SQL Server for Stored Procedure that was Generated at Runtime by using try catch or error handling in SQL Server.

First to do this we have to Understand What is the Exception or Exception Handling in Stored Procedure in SqlServer.

What is Exception:-

Exception means one kind of Error that was Generated at run time so user will not know this type of Error are Generated at Runtime.

For Example:-

If user Divided any Number by 0(zero) so it will throws Exception at Runtime so handle this type of Exception Sqlserver provide one technique Called ExceptionHandling

Here is the Image of types of exception

 

Description:

 To handle exceptions in SQL Server that was Generated at Runtime so we have to use TRY…… CATCH blocks. To use TRY…… CATCH  blocks in stored procedure we need to write the query like as shown below


Syntax For Handling Exception in SqlServer Stored Procedure:-

BEGIN TRY
---Write Your Code
END TRY
BEGIN CATCH
---Write Code to handle errors
END CATCH

In TRY block we will write our queries and our Logic or any Function that are used in Sql Query Like Sum() of the Sql and in CATCH block we will write code to handle exceptions that is Generated in TRY Block at Runtime Depend on Sitution. In our SQL statements if any error occurs automatically it will move to CATCH block in that we can handle error messages. To handle error messages we have defined Error Functions in CATCH block those are

ERROR_LINE() - This function will return error line number of SQL query which cause to raise error.

ERROR_NUMBER() - This function will return error number which is unique and assigned to it.

ERROR_SEVERITY() - This function will return severity of error which indicates how serious the error is. The values are between 1 and 25.

ERROR_STATE() - This function will return state number of error message which cause to raise error.

ERROR_PROCEDURE() - This function will return name of the procedure where an error occurred.

ERROR_MESSAGE() - This function will return the complete text of the error message which cause to raise error.

Check below sample query to handle errors in stored procedure

Example of Procedure with Try Catch Block:-

USE [Mint]

GO

/****** Object:  StoredProcedure [dbo].[sp_WarehouseMaster_Create]    Script Date: 09/07/2013 10:57:57 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        kirit kapupara

-- Create date: 02/09/2013

-- Description:   Create_WarehouseMast

-- =============================================

ALTER PROCEDURE [dbo].[sp_WarehouseMaster_Create]

         @fk_companyid bigint

      ,@fk_userid int

      ,@fk_floornoid int

      ,@fk_storageunitsid int

      ,@warehousenoorname varchar(50)

      ,@warehouseincharge int

      ,@address varchar(MAX)

      ,@storagecapacity float

      ,@photo image

      ,@warehousetype int

      ,@ERROR NVARCHAR(MAX)

AS

      BEGIN

            SET NOCOUNT ON;

           

            BEGIN TRY

            INSERT INTO [Mint].[mint2].[WareHouseMast]

           (fk_companyid

           ,fk_userid

           ,fk_floornoid

           ,fk_storageunitsid

           ,warehousenoorname

           ,warehouseincharge

           ,address

           ,storagecapacity

           ,photo

           ,warehousetype)

         

     VALUES

           (@fk_companyid

           ,@fk_userid

           ,@fk_floornoid

           ,@fk_storageunitsid

           ,@warehousenoorname

           ,@warehouseincharge

           ,@address

           ,@storagecapacity

           ,@photo

           ,@warehousetype)

                 

            END TRY

           

            BEGIN CATCH

            SET @ERROR= 'errormessage :'+ ERROR_MESSAGE() +' '+ 'errorseverity :'+CAST(ERROR_SEVERITY() as nvarchar(500)) +' '+ 'errorstate :'+CAST(ERROR_STATE() as nvarchar(500))+' '+ 'errornumber :'+CAST(ERROR_NUMBER() as nvarchar(500)) +' '+ 'storedprodcedurename :'+ERROR_PROCEDURE();

            RAISERROR(@Error,11,12);

           

            END CATCH

      END

If we run above query we will get output like as shown below