Inheritence of Set Transaction Isolation Level?

[Jeff Moden]

Greetings,

I have a "main" stored procedure that executes other stored procedures and it looks something like this...

Set Transaction Isolation Level READ UNCOMMITTED
.
.
.
EXEC (storedproc1)
EXEC (storedproc2)
EXEC (storedproc3)
.
.
.
Return

My question is: Will the EXEC'd stored procedures run as if the Transaction Isolation Level had been explicitly set within those stored procedures? In other words, do the EXEC'd stored procedures inherit the Transaction Isolation Level of the "main" stored procedure?

Thank you for your time...

[skhanal]

Hmmm..

I think it would, did you test it?

[Jeff Moden]

Thank you for your reply but if I knew how to test it, I wouldn't have needed to post for help. If you know a definitive method to test this, I'm all ears.

[skhanal]

create two procedures in pubs database,
sp1 as
select * from authors
exec sp2

sp2 as
select * from titles

In Query analyzer, start a new windows and run this

begin tran
update authors set au_fname='Test'

update titles set titlename='test title'

in second query analyzer window run

Set Transaction Isolation Level READ UNCOMMITTED

exec sp1

if you get results from both tables the isolation level is inherited, if you get results from authors only and it is blocked then it is not inherited.

[Jeff Moden]

Thank you for your time... I'll give it a try.