Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts

Thursday, October 16, 2014

Error handler: TRY CATCH

BEGIN TRANSACTION TRAN1

BEGIN TRY

INSERT INTO.....

UPDATE.....

END TRY

BEGIN CATCH

If @@TRANCOUNT > 0

--error happened

ROLLBACK TRANSACTION TRAN1

END CATCH

If @@TRANCOUNT > 0

COMMIT TRANSACTION TRAN1

GO

Friday, August 17, 2012

Error handling in transactions in sql 2008

BEGIN TRANSACTION TRAN1

BEGIN TRY
--put all the valid statements here--


   CREATE TABLE #test(intREsult int);

   INSERT INTO #test Values (4/0);
   INSERT INTO #test Values (1/2);


  COMMIT TRAN TRAN1
END TRY


BEGIN CATCH
--the error handling code including the rollback

    DECLARE @errorMEssage nvarchar(1000);
   SELECT @errorMEssage = ERROR_MESSAGE();
   ROLLBACK TRAN TRAN1;
   RAISERROR(@errorMEssage,16,1);

END CATCH