July 18, 2008 at 9:31 am
I try during a transaction raise error but @n return me always 112....not 100.
Where I wrong. Thank you Alen, Italy
------------------------------------------------
SET XACT_ABORT ON
BEGIN TRY
DECLARE @n int
DECLARE @MyTran VARCHAR(20);
SET @MyTran = 'Alter views';
SET @n = 100
print @n
BEGIN TRANSACTION @MyTran
PRINT 'start trans'
SET @n = 112
RAISERROR ('my err',11,1)
COMMIT TRANSACTION @MyTran
PRINT @n
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRANSACTION @MyTran
print @n
print 'Transaction roolback'
END
IF (XACT_STATE()) = 1
BEGIN
COMMIT TRANSACTION @MyTran
print @n
print 'Transaction commit'
END
END CATCH
July 18, 2008 at 10:12 am
Roll-back Transaction will only roll-back data modications and will not affect the values in local variables.
so @n stays = 112
July 21, 2008 at 1:29 am
You need to reset the variable in the Catch Block.
Sometimes you need to reset the variables as they were at the start of the procedure in case of an error. this can only be done by re-initializing the variables in Catch Block as the variabels are not and cannot be reinitialized by ROLLBACK.
Atif Sheikh
July 21, 2008 at 1:45 am
OK perferct. Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply