VistaDB 5
Gibraltar VistaDB > Developer's Guide > How To Perform Common Tasks > How To - Handle Transactions in SQL
How To - Handle Transactions in SQL

In a SQL Proc you may want to check for transactions using the @@TRANCOUNT system variable.

The query will return 0 for none, or a positive number (only 1 is valid at this time). If no database is open or if the database is in an invalid state -1 will be returned to represent an error condition.

Open Transaction Count in SQL
Copy Code
SELECT @@TRANCOUNT; 

A stored procedure capable of handling an exception in a TSQL Proc that contains transactions should also check the TRANCOUNT and rollback if it is found to be greater than 0.

SQL Stored Procedure Example
Copy Code
BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
-- Do we have a transaction to rollback?
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
END CATCH 
See Also