question

Papillon28-9167 avatar image
0 Votes"
Papillon28-9167 asked ErlandSommarskog answered

ALTERNATIVE of @@TRANSCOUNT in SQL

Hi All,

SQL Server 2016 version
I am using procedure code with following format

BEGIN TRY

 BEGIN TRANSACTION

    SQL INSERT STATEMENTS

COMMIT TRANSACTION

END TRY

BEGIN CATCH

IF @@trancount > 0 ROLLBACK TRANSACTION

END CATCH


My question is how to stop using @@trancount in procedure code? Any alternative for @@trancount ??

T.I.A

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

I agree with Tom, outlawing @@trancount is lunacy. But you can use this instead:

IF xact_state() <> 0 ROLLBACK TRANSACTION

But do not simply comment out @@trancount. You cannot do a ROLLBACK without checking for an open transaction. That transaction may already have been rolled back by an inner scope when you arrive in the CATCH handler.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 edited

Why do you want to not use @@trancount?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Papillon28-9167 avatar image
0 Votes"
Papillon28-9167 answered Papillon28-9167 commented

SQL Syntax not allowed as per directions so looking for alternatives.
T.I.A

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You stated you are using SQL 2016, that is the proper syntax. Do you have an error message?

0 Votes 0 ·

No error.. in fact it's working fine but due to some direction given by my team we are trying to stop using few syntaxes..

So can I simply comment out as below and only use Rollback transaction?

--- IF @@trancount > 0

ROLLBACK TRANSACTION



T.I.A

0 Votes 0 ·

There is no need to remove @@TRANCOUNT . It is valid TSQL command.

If you must, in your code your first statement after BEGIN TRY is a BEGIN TRAN, so it should be safe to ignore @@TRANCOUNT and simply rollback in your catch block.

0 Votes 0 ·
Show more comments