This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(230.39999999999998, 59%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESA%3C/text%3E%3C/svg%3E)
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
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
Why do you want to not use @@trancount?
SQL Syntax not allowed as per directions so looking for alternatives.
T.I.A
You stated you are using SQL 2016, that is the proper syntax. Do you have an error message?
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
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.
Yes you are right in my procedure code my first statement after BEGIN TRY is a BEGIN TRANSACTION
So I will comment out
@@TRANCOUNT
Safely :)
T.I.A