Transactions SQL 2000 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Transactions SQL 2000

Author

Topic

JavierVera
Starting Member

4 Posts

Posted - 2013-10-24 : 12:15:18

I have the following code:

DECLARE @SERVICIO CHAR(2)

SET @SERVICIO = '11'

BEGIN TRANSACTION GET_NEXT_CORR
DECLARE @NEXT_VALUE INT

SET @NEXT_VALUE = (
SELECT TOP 1 (A.valor + 1)
FROM correlativo A
WHERE NOT EXISTS
(SELECT 1
FROM correlativo B
WHERE B.valor = (A.valor + 1))
AND A.valor NOT IN
(SELECT MAX(C.valor)
FROM correlativo C)
ORDER BY 1)

IF @NEXT_VALUE IS NULL
BEGIN
SET @NEXT_VALUE = (SELECT ISNULL(MAX(valor)+1,1) FROM correlativo)
INSERT INTO correlativo VALUES (@SERVICIO, @NEXT_VALUE)
IF (@@ERROR <> 0)
BEGIN
PRINT '1'
GOTO CORR_HANDLER
END

END
ELSE
BEGIN
INSERT INTO correlativo VALUES (@SERVICIO, @NEXT_VALUE)
IF (@@ERROR <> 0)
BEGIN
PRINT '2'
GOTO CORR_HANDLER
END
END

IF (@@ERROR <> 0)
BEGIN
PRINT '3'
GOTO CORR_HANDLER
END

COMMIT TRANSACTION GET_NEXT_CORR

CORR_HANDLER:
PRINT 'ERROR'
PRINT @@ERROR
PRINT @@TRANCOUNT
ROLLBACK TRANSACTION GET_NEXT_CORR

I would like to know why i keep getting an error that ROLLBACK has not the proper BEGIN TRAN...

What am i missing there??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-24 : 12:29:23

Because it's hitting the rollback tran regardless. If this is for a stored procedure, add RETURN after the COMMIT so it never hits the rollback code section. Otherwise you'll need to add an IF to the CORR_HANDLER section.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

JavierVera
Starting Member

4 Posts

Posted - 2013-10-24 : 12:48:33

Thanks, you made me realize how this works at least...

I wonder why keeps inserting when i get an error???? it shouldnt be on the rollback point and undo the inwsert???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-24 : 13:15:23

Are you referring to the ROLLBACK error? That error occurs after the insert/commit...

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

JavierVera
Starting Member

4 Posts

Posted - 2013-10-24 : 16:58:51

I think i didnt make myself clear but its fine, i was referring to the atomicity of my transaction.
I ensure this function with the @@ERROR variable.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-24 : 17:03:01

You need to store the value of @@ERROR into a variable and use that inside CORR_HANDLER as you are resetting @@ERROR with your PRINT messages after the inserts.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

JavierVera
Starting Member

4 Posts

Posted - 2013-10-25 : 15:52:18

yup, thanks.

heres the snippet wich i use to keep a sort of control:

DECLARE @ErrorCode INT
DECLARE @RowsAffected INT

UPDATE TABLE SET FIELD = A WHERE = B -- <<<< After each possible cause of error i update the variables value
SET @ErrorCode = @@ERROR -- <<<< After each possible cause of error i update the variables value
SET @RowsAffected = @@ROWCOUNT -- <<<< After each possible cause of error i update the variables value

IF(@ErrorCode <> 0) BEGIN
GOTO TRX_HANDLER
END
ELSE IF(@RowsAffected > 0) BEGIN
SET @piCantCerr = @piCantCerr +1
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources