Transaction Support with Error Handling in SQL-Server

Transaction Support with Error Handling in SQL-Server

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[TestSP]

@RetValue int OUTPUT

AS

DECLARE @error int

BEGIN

BEGIN TRAN

INSERT INTO ERRORLOG VALUES('TESTING')

INSERT INTO TESTTABLE VALUES(1,2.356)

set @error=@@ERROR

IF @error <> 0 -- Error

BEGIN

SET @RetValue= -2

goto errorlog

END

ELSE

BEGIN

COMMIT TRAN

SET @RetValue = scope_identity()

END

PRINT @RetValue

errorlog:

ROLLBACK TRAN

END

Executing the above First time – Insert one row in both the tables

Executing the above First time – Insert one row in ‘ErrorLog’ table then rollback it as the error occurs while inserting in ‘TestTable’.

Thanks & Regards,

Arun Manglick || Tech Lead