VistaDB 6
VistaDB / Developer's Guide / How To Perform Common Tasks / How To - Handle Transactions in SQL
In This Topic
    How To - Handle Transactions in SQL
    In This Topic

    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