Managing a sql transaction with handling errors

Question

Managing a sql transaction with handling errors

pmscorca 1,052

Hi, I need to design a T-SQL stored procedure to manage at better a transaction (begin tran ... commit ... rollback) with handling possible errors (try ... catch). Any suggests to me in order to nest rightly the T-SQL code? Thanks

0 comments

1 answer

Your answer

Answer 1

Olaf Helper 47,621

You post is much to vague to give a specific/meanful answer on.

Any suggests to me in order to nest rightly the T-SQL code

What do you want to "nest" here (where)?

pmscorca 1,052

A sample code could be this:

BEGIN TRY
	BEGIN TRANSACTION
	/*         T-SQL Code     */
	COMMIT TRANSACTION
END TRY
BEGIN CATCH     
	DECLARE @ErrorMessage NVARCHAR(4000);    
	DECLARE @ErrorSeverity INT;    
	DECLARE @ErrorState INT;     
	SELECT 
		@ErrorMessage = ERROR_MESSAGE(),
		@ErrorSeverity = ERROR_SEVERITY(),
		@ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, -- Message text.
			@ErrorSeverity, -- Severity.
			@ErrorState -- State.
			);

	WHILE @@TRANCOUNT > 0
		BEGIN
			ROLLBACK TRANSACTION;
		END
END CATCH

Could it a good practice? Thanks

pmscorca 1,052

A sample could be this:

BEGIN TRY
	BEGIN TRANSACTION
		/*         T-SQL Code     */
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SELECT 
		@ErrorMessage = ERROR_MESSAGE(),
		@ErrorSeverity = ERROR_SEVERITY(),
		@ErrorState = ERROR_STATE();
	
	RAISERROR (@ErrorMessage, -- Message text.
			@ErrorSeverity, -- Severity.
			@ErrorState -- State.
			);
	WHILE @@TRANCOUNT > 0
		BEGIN
			ROLLBACK TRANSACTION;
		END
END CATCH

Could it be a good practice? Thanks

Share via

Managing a sql transaction with handling errors

1 answer

Your answer