Transaction with Try Catch Snippet

wkhazzard

May I suggest a snippet to include in future releases?

Snippet: ttc
Description: Transaction with Try Catch and Error Reporting, Contributed by Kevin Hazzard (DevJourney.com)

BEGIN TRANSACTION;
BEGIN TRY
	$CURSOR$;
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0
		ROLLBACK TRANSACTION;
	PRINT N'Exception while processing ...';
	PRINT N'  Error number    = ' + CONVERT(NCHAR(10),ERROR_NUMBER());
	PRINT N'  Error severity  = ' + CONVERT(NCHAR(10),ERROR_SEVERITY());
	PRINT N'  Error state     = ' + CONVERT(NCHAR(10),ERROR_STATE());
	PRINT N'  Error procedure = ' + ERROR_PROCEDURE();
	PRINT N'  Error line      = ' + CONVERT(NCHAR(10),ERROR_LINE());
	PRINT N'  Error message   = ' + ERROR_MESSAGE();
END CATCH

IF @@TRANCOUNT > 0
BEGIN
	COMMIT TRANSACTION;
	PRINT N'Success';
END
ELSE
BEGIN
	PRINT N'Failure';
END

Anu D

Many thanks for your post.

I will forward your request to the respective Product manager to include it by default.