What are the challenges you face when working across database platforms? Take the survey
Options

Transaction with Try Catch Snippet

wkhazzardwkhazzard Posts: 20
edited March 11, 2011 8:39AM in SQL Prompt Previous Versions
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
Kevin Hazzard, C# MVP & Friend of Red Gate
http://devjourney.com

Comments

Sign In or Register to comment.