Is my SQL query running inside transaction scope?

Just another tip I got during inspecting a query written for a SSIS package. ie from the same incident which I explained in my last post about SQL. There was some part of SQL stored procedure which is not supposed to run in transaction. Since we don’t have transaction suppression support in SQL Server, the alternative took was as follows.

• Find out whether the stored procedure is running in transaction.
•  If so throw error. Since this SSIS package is supposed to be called from multiple places we cannot assume that it will not be called without transaction.

How to check for presence of transaction inside SQL SP

Its simple as checking the return value of XACT_STATE() function anywhere in SQL.The code snippet is given below for reference.

IF XACT_STATE() <> 0
BEGIN
    DECLARE @ProcName sysname = OBJECT_NAME(@@PROCID);
    RAISERROR('Stored procedure "%s" cannot be executed in a transaction.', 16, 1, @ProcName)
    RETURN;
END;

Putting it in a SP.

CREATE PROCEDURE dbo.spWillNotRunInTransaction 
AS
BEGIN
IF XACT_STATE() <> 0
BEGIN
    DECLARE @ProcName sysname = OBJECT_NAME(@@PROCID);
    RAISERROR('Stored procedure "%s" cannot be executed in a transaction.', 16, 1, @ProcName)
    RETURN;
END;
SELECT 'I am Independent...'
END
GO

Real time application

One of the real time use I could see is to simulate the .net model transaction suppression as mentioned below inside any stored procedure.

using (TransactionScope txScope =
           new TransactionScope(TransactionScopeOption.Suppress))
    {
        // Code indluding DB queries
        txScope.Complete();
    }

A link I obtained which explains about the unavailability of transaction suppression in SQL Server and how to tackle the situation by alternatives.
http://blogs.msdn.com/b/sqlprogrammability/archive/2008/08/22/how-to-create-an-autonomous-transaction-in-sql-server-2008.aspx