example for store procedure with both transcration and error handling

USE [Limo_DB]
GO
/****** Object:  StoredProcedure [dbo].[SP_Biz_Approve_Import]    Script Date: 09/06/2014 17:59:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[SP_Biz_Approve_Import]
@Id int,
@IsImmediate bit,
@ApprovedBy int,
@OutMsg varchar(100) output
as
begin
	
	begin try
	begin tran
	declare @Type nvarchar(1)
	
	if(@IsImmediate is null)
	begin
		update dbo.Import_Info set Checker=@ApprovedBy,
		Is_Immediate=null, Status='Cancelled' 
		where Id=@Id and ([Status]='Pending' or [Status]='Confirmed')
		SET @OutMsg='S'
	end
	else
	begin
		update dbo.Import_Info set Checker=@ApprovedBy,
		Is_Immediate=@IsImmediate, Status=case @IsImmediate when 0 then 'Confirmed' 
		when 1 then 'Complete' end,
		Upload_To_Info_Date=case @IsImmediate when 0 then NULL
		when 1 then GETDATE() end
		where Id=@Id and ([Status]='Pending' or [Status]='Confirmed')
	
	select @Type=[Type] from Import_Info where Id=@Id
	
	if (@IsImmediate=1)
	begin
		exec [SP_Execute_Import] @Id,@Type
	end
	
	SET @OutMsg='S'
	end
	

	COMMIT TRAN
	END TRY
	
	BEGIN CATCH
		IF @@TRANCOUNT>0
		BEGIN
		   SET @OutMsg='F'	
		   ROLLBACK TRAN
		   INSERT INTO [dbo].[SYS_ServiceLog] VALUES(GETDATE(),'ERROR','SP_Biz_Approve_Import',ERROR_MESSAGE(),NULL)
		END
	END CATCH
end

　　the following function also are available in CATCH block:

 ERROR_NUMBER()
 ERROR_MESSAGE()
 ERROR_SEVERITY()
 ERROR_STATE()
 ERROR_LINE()
 ERROR_PROCEDURE()

 

转载于:https://www.cnblogs.com/Jenny90/p/3961578.html