/*
CREATE PROCEDURE dbo.usp_PrimaryKeyErrorHandler
AS
BEGIN
	DECLARE @Error_Number int, @Error_Severity int, @Error_State int,
		@Error_Procedure varchar(1000), @Error_Line int, @Error_Message varchar(8000)
	SELECT
		@Error_Number = ERROR_NUMBER(),
		@Error_Severity = ERROR_SEVERITY(),
		@Error_State = ERROR_STATE(),
		@Error_Procedure = ERROR_PROCEDURE(),
		@Error_Line = ERROR_LINE(),
		@Error_Message = ERROR_MESSAGE()
	
	--Handle errors
	IF  @Error_Number = 2627
		--Primary key violation, return error to client
		BEGIN
			RAISERROR(@Error_Message,@Error_Severity, @Error_State)	
		END
	ELSE
		--just return error to the app nicely if you want to do something with it
		BEGIN
			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
END
*/
SET NOCOUNT ON
BEGIN TRY
    SELECT 1/0
END TRY
BEGIN CATCH
	Exec usp_PrimaryKeyErrorHandler
END CATCH

BEGIN TRY
	Create Table #Temp (id int primary key)
	Insert Into #Temp (id) Values(1) 
	Insert Into #Temp (id) Values(1) 
END TRY
BEGIN CATCH
	Exec usp_PrimaryKeyErrorHandler
	Drop Table #Temp
END CATCH

DECLARE @Err int ,@msg_id bigint
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

 

    BEGIN TRY
	INSERT vw_Billing WITH(ROWLOCK)......
	VALUES .....
    END TRY 
   BEGIN CATCH
		SET @Err = @@ERROR
		SELECT @ErrorMessage = ERROR_MESSAGE(),
			   @ErrorSeverity = ERROR_SEVERITY(),
			   @ErrorState = ERROR_STATE();
		SET @msg_id = 50000+@Err
		END CATCH
	IF(@Err=0)
		BEGIN
			RETURN 1
		END
	ELSE
		BEGIN
			IF @Err = 2767 RETURN 0 --duplicate Key Error
			print @ErrorSeverity --=>16
			print @ErrorState--=>1
			RAISERROR(@msg_id,@ErrorSeverity,@ErrorState)
			--SET @@ERROR = 
			RETURN 0
		END