What's my transaction isolation level

That's an easy question to answer - StackOverflow has a fine answer.

But, what if I use sp_executesql to run some dynamic sql - does it default the connection isolation level? If I change isolation level within the query, does it propagate back to the invoker? That's a great question, William. Let's find out.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT CASE transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'ReadUncommitted' 
WHEN 2 THEN 'ReadCommitted' 
WHEN 3 THEN 'Repeatable' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID;

DECLARE
    @query nvarchar(max) = N'-- Identify iso level
SELECT CASE transaction_isolation_level 
WHEN 0 THEN ''Unspecified'' 
WHEN 1 THEN ''ReadUncommitted'' 
WHEN 2 THEN ''ReadCommitted'' 
WHEN 3 THEN ''Repeatable'' 
WHEN 4 THEN ''Serializable'' 
WHEN 5 THEN ''Snapshot'' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Test iso level
SELECT CASE transaction_isolation_level 
WHEN 0 THEN ''Unspecified'' 
WHEN 1 THEN ''ReadUncommitted'' 
WHEN 2 THEN ''ReadCommitted'' 
WHEN 3 THEN ''Repeatable'' 
WHEN 4 THEN ''Serializable'' 
WHEN 5 THEN ''Snapshot'' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID'

EXECUTE sys.sp_executesql @query, N'';

SELECT CASE transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'ReadUncommitted' 
WHEN 2 THEN 'ReadCommitted' 
WHEN 3 THEN 'Repeatable' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID;

I begin my session in read uncommitted aka "nolock". I then run dynamic sql which identifies my isolation level, still read uncommitted, change it to a different level, confirmed at read committed, and then exit and check my final state - back to read uncommitted.

Finally, thanks to Andrew Kelly (b|t) for answering the #sqlhelp call.