Error handling inside stored procedure

,

Following script gives an example how to handle errors inside stored procedure with returning information about type of error occured

create proc _Err_Sp
	@Emp_Code char(5),
	@Name_Type char(1)= 'O',   ----'O'- organization; 'P' - person
	@Client_Name varchar(120),  -- for person "Last Name"
	@First_Name varchar(20) = '',
	@Middle_Name varchar(20) = '',
	@Currency char(3) ='USD'

as

/*
This procedure created entry in the Name table for client with indication if it's person or organization.
Passing parameters include Employee code, insert info,type 
Validation have to be done on employee code and type
*/
declare	@Emp_ID int,
	@Full_Client_Name char(120),
	@Client_Name_Rept char(40),
	@ErrNum int, 
	@RowCount int,
	@myerror varchar (50),
	@err_emp int ,
	@err_type int

	set @err_emp =30001
	set @err_type = 30002
	set @MyError =""

set nocount on	

IF @@TRANCOUNT=0
	BEGIN TRAN

	select @Emp_ID =  EMP_ID from tbl_PERSNL where EMPLOYEE_CODE = @Emp_Code
	select @RowCount = @@ROWCOUNT, @ErrNum = @@ERROR
	if   @ErrNum <> 0 GOTO myerror

	IF @RowCount = 0 
		begin
			set @myerror = "Employee Code is Invalid "
			set @ErrNum =@err_emp
			GOTO myerror
		end

	if @Name_Type ='P' 
	   BEGIN
		select @Full_Client_Name =rtrim( rtrim(@Client_Name)+ ','+rtrim(coalesce(@First_Name,''))+ ' ' +rtrim(coalesce(@Middle_Name,'')))
		select 	@Client_Name_Rept = left(@Full_Client_Name,40)

		insert into TBL_NAME
			(MAIN_NAME,LAST_NAME,FIRST_NAME,MIDDLE_NAME,
			LANGUAGE_CODE,CURRENCY)
		values ( @Full_Client_Name,@Client_Name,@First_Name,@Middle_Name,'EN',@Currency)
		set @ErrNum = @@ERROR
		if   @ErrNum <> 0 GOTO myerror

	   END	
	else if @Name_Type ='O'
	   BEGIN
		select 	@Client_Name_Rept = left(@Client_Name,40)
		insert into TBL_NAME
			(MAIN_NAME,NAME_REPT,LANGUAGE_CODE,CURRENCY)
		values (@Client_Name,@Client_Name_Rept,'EN',@Currency)
		set @ErrNum = @@ERROR
		if   @ErrNum <> 0 GOTO myerror

	   END
	ELSE
	   BEGIN
		set @myerror = "Name Type is Invalid "
		set @ErrNum =@err_type
		GOTO myerror
	   END
	
	COMMIT TRAN 

set nocount off
RETURN 0

myerror:
BEGIN

	if @@trancount<>0 
	begin
	 	ROLLBACK TRAN
		select 'Rolling Back Client'
	end
	IF len(ltrim(rtrim(@myerror)))= 0
/* If it's system error */
		SELECT @myerror = (SELECT  description FROM  master..sysmessages WHERE error = @ErrNum)
		
	raiserror @errnum @myerror
	
	set nocount off
	RETURN @ErrNum
END

Rate

3 (2)

Share

Share

Rate

3 (2)