Scope of SET TRANSACTION ISOLATION LEVEL

To help you better understand the scope of SET TRANSACTION ISOLATION LEVEL, I wrote some code for you to run on your own machine to experiment with and see for yourself. I think you'll remember it better by running the script below and carefully analyzing the output.

Whatever the TRANSACTION ISOLATION LEVEL is before executing a stored procedure is what it will be when the stored procedure starts executing. If it is changed inside the stored procedure, the change will persist within the stored procedure, but it will not persist outside the stored procedure.

If sp_executesql is executed within the stored procedure, the current TRANSACTION ISOLATION LEVEL in the stored procedure is carried through into the statement executed by sp_executesql. This is not a trivial point. There are some things in a stored procedure that don't carry through into sp_executesql. For example, a table valued function declared in a stored procedure isn't scoped into sp_executesql, but a temporary table declared in a stored procedure does scope into sp_executesql, which is why I used a temporary table instead of a table variable.

Documentation for sys.dm_exec_requests and transaction_isolation_level is found here. Quoting from said document, we see the following definitions of transaction_isolation_level:

      0 = Unspecified
      1 = ReadUncomitted
      2 = ReadCommitted
      3 = Repeatable
      4 = Serializable
      5 = Snapshot

I recommend setting SSMS to display the results as text instead of in a grid. Go to Query menu, then select Results To and from that menu, select Results to Text. Or more simply, do CTRL-T to accompish the same thing.

Have fun!

SET NOCOUNT ON

USE tempdb;
GO

CREATE PROCEDURE dbo.TIL
AS
    DECLARE @transaction_isolation_level SMALLINT;
    DECLARE @sqlString NVARCHAR(1000);

    CREATE TABLE #TILstate (
        context NVARCHAR(100)
       ,transaction_isolation_level SMALLINT
    );

    SELECT @transaction_isolation_level = transaction_isolation_level
    FROM sys.dm_exec_requests
    WHERE session_id = @@spid;

    INSERT INTO #TILstate (context, transaction_isolation_level)
    VALUES ('in stored proc, initial transaction_isolation_level before changing anything', @transaction_isolation_level);

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    SELECT @transaction_isolation_level = transaction_isolation_level
    FROM sys.dm_exec_requests
    WHERE session_id = @@spid;

    INSERT INTO #TILstate (context, transaction_isolation_level)
    VALUES ('in stored proc, after SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED', @transaction_isolation_level);

    SET @sqlString = 'INSERT INTO #TILstate (context, transaction_isolation_level)'
                   + ' SELECT ''in stored proc, transaction_isolation_level in sp_executesql'','
                   + ' transaction_isolation_level FROM sys.dm_exec_requests WHERE session_id = @@spid';

    PRINT @sqlString;
    PRINT '';

    EXEC sp_executesql @sqlString;

    SELECT * FROM #TILstate;

    DROP TABLE #TILstate;

GO --need this statement to separate the CREATE PROCEDURE from what's next

/* If you execute the following statements a second time, you're likely to see slightly different results. */
/* You should be able to figure out why by carefully considering what condition changed. */

SELECT N'default transaction_isolation_level before executing anything' as context, transaction_isolation_level
FROM sys.dm_exec_requests
WHERE session_id = @@spid;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SELECT N'transaction_isolation_level after setting it and before executing stored procedure' as context, transaction_isolation_level
FROM sys.dm_exec_requests
WHERE session_id = @@spid;

exec dbo.TIL;

SELECT N'transaction_isolation_level after executing stored procedure' as context, transaction_isolation_level
FROM sys.dm_exec_requests
WHERE session_id = @@spid;