CODEWITHSUNDEEP
sqlsql-server
user3407335
user3407335

Reputation: 136

When setting transaction level in a stored procedure, do the procs that get called stay at the same transaction level?

I am trying to be certain that the stored procedures underneath my main calling stored procedure get executed with the same transaction isolation level.

For example, if I set SET TRANSACTION ISOLATION LEVEL SNAPSHOT, will it stay snapshot if I call another stored procedure within my original stored procedure?

CREATE PROCEDURE [dbo].[FirstStoredProc]
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT

    EXEC [dbo].[SecondStoredProc]
END;

CREATE PROCEDURE [dbo].[SecondStoredProc]
AS
BEGIN
    SELECT name
    FROM sys.objects
    WHERE type = 'P';
END;

EXEC FirstStoredProc

Will the select be run under snapshot isolation?

Upvotes: 0

Views: 321

Answers (2)

benjamin moskovits
benjamin moskovits

Reputation: 5458

Its in the context of the calling proc. To see this add this line to the proc, SecondStoredProc:

dbcc useroptions;

and the last line will have an entry for isolation level.

Upvotes: 1

user3407335
user3407335

Reputation: 136

I found it from another stackoverflow question. It does keep its scope.

ALTER PROCEDURE [dbo].[FirstStoredProc] 
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT
    SELECT 'PROC 1'
    DBCC USEROPTIONS;
EXEC [dbo].[SecondStoredProc]
END;
ALTER PROCEDURE [dbo].[SecondStoredProc] 
AS
BEGIN
--Select name from sys.objects where type = 'P';
SELECT 'PROC 2'
DBCC USEROPTIONS;
END;

DBCC USEROPTIONS;
EXEC FirstStoredProc

Upvotes: 0

Related Questions