How to find out whether the stored procedure is running in transaction.

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s