Store Procedure for crud operation with transaction and exception handling (try catch block)

CREATE TABLE [dbo].[tblCustomer](
[CustID] [bigint] NOT NULL,
[CustName] [nvarchar](50) NULL,
[CustEmail] [nvarchar](50) NOT NULL,
[CustAddress] [nvarchar](256) NULL,
[CustContact] [nvarchar](50) NULL,
 CONSTRAINT [PK_tblCustomer] PRIMARY KEY CLUSTERED
(
[CustID] ASC,
[CustEmail] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE PROCEDURE [dbo].[SP_CUSTOMER_INSERT]

(

@CustName NVarchar(50)  

    ,@CustEmail NVarchar(50)  

    ,@CustAddress NVarchar(256)  

    ,@CustContact  NVarchar(50)  

)  

AS BEGIN

SET NOCOUNT ON;

BEGIN TRY

BEGIN TRANSACTION

DECLARE @CustID Bigint

  SET @CustID = isnull(((SELECT max(CustID) FROM [dbo].[tblCustomer])+1),'1')  

  Insert INTO tblCustomer(CustID,CustName,CustEmail,CustAddress,CustContact)Values(@CustID,@CustName,@CustEmail,@CustAddress,@CustContact)

  Select 1

  Commit Transaction

  End Try

  BEGIN CATCH

   DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity INT,@ErrorState INT;  

            SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();  

            RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);  

Rollback Transaction

END Catch

End

GO

CREATE  PROCEDURE [dbo].[SP_CUSTOMER_DELETE]

@CustID BIGINT  

AS

BEGIN

SET NOCOUNT ON;

BEGIN TRY

BEGIN TRANSACTION

DELETE tblCustomer

 WHERE [CustID] = @CustID   

        SELECT 1  

COMMIT TRANSACTION

END TRY

BEGIN CATCH

DECLARE @ERROR_MEASSAGE NVARCHAR(4000),@ERRORSERVERITY INT , @ERRORLINE INT ,@ERRORSTATE INT;

SELECT @ERROR_MEASSAGE=ERROR_MESSAGE(),@ERRORSERVERITY=ERROR_SEVERITY(),@ERRORLINE=ERROR_LINE(),@ERRORSTATE=ERROR_STATE();

RAISERROR (@ERROR_MEASSAGE ,@ERRORSERVERITY,@ERRORLINE,@ERRORSTATE);

ROLLBACK TRANSACTION

END CATCH

END

CREATE PROCEDURE [dbo].[SP_CUSTOMER_UPDATE]

 @CustID BIGINT 

    ,@CustName NVarchar(50) =NULL

    ,@CustEmail NVarchar(50)  =NULL

    ,@CustAddress NVarchar(256) =NULL 

    ,@CustContact  NVarchar(50) =NULL 

AS

BEGIN

SET NOCOUNT ON

BEGIN TRY

BEGIN TRANSACTION

UPDATE [dbo].[tblCustomer]

SET CustName=@CustName,

CustAddress=@CustAddress,

CustContact=@CustContact

WHERE CustID=@CustID AND [CustEmail]=@CustEmail

COMMIT TRANSACTION

END TRY

BEGIN CATCH 

SELECT  1    

DECLARE  @ErrorMessage  NVARCHAR(200),@ErrorSeverity INT ,@ErrorState int,@ErrorLinenumber INT;

select  @ErrorMessage =ERROR_MESSAGE(),@ErrorSeverity=ERROR_SEVERITY(), @ErrorState=ERROR_STATE(),@ErrorLinenumber=ERROR_LINE();

SELECT @ErrorMessage,@ErrorSeverity,@ErrorState,@ErrorLinenumber

RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState,@ErrorLinenumber)

ROLLBACK TRANSACTION

END CATCH

END

CREATE PROCEDURE [dbo].[SP_SELECT_CUSTOMER_BY_ID]

@CustID  BIGINT

AS BEGIN

SET NOCOUNT ON;

BEGIN TRY

BEGIN TRANSACTION

SELECT * FROM [dbo].[tblCustomer]

WHERE CustID=@CustID

SELECT 1

COMMIT TRANSACTION

END TRY

BEGIN CATCH 

DECLARE @ERRORMESAGE NVARCHAR(400),@ERRORSERVERITY INT,@ERRORSTATUS INT, @ERRORLINE INT,@ERRORPROCEDURE NVARCHAR(50);

SELECT @ERRORMESAGE=ERROR_MESSAGE(),@ERRORsERVERITY=ERROR_SEVERITY(),@ERRORSTATUS=ERROR_STATE(),@ERRORLINE=ERROR_LINE(),

@ERRORPROCEDURE=ERROR_PROCEDURE();

RAISERROR (@ERRORMESAGE,@ERRORsERVERITY,@ERRORSTATUS,@ERRORLINE,@ERRORPROCEDURE)

ROLLBACK TRANSACTION

END CATCH

END

---------------------------------------------------------------------
Note: -Default exception in SQL Server 2014 is

ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage;

The severity parameter specifies the severity of the exception.