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 |
|