Isolation Level for stored procedure SQL Server?

Question

I want to add Isolation level in my procedure and for that I wanted to confirm that which one is the correct format from below:

Attempt #1 - setting isolation level before calling the stored procedure:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

EXEC [sp_GetProductDetails] 'ABCD','2017-02-20T11:51:37.3178768'

Attempt #2 - setting isolation level inside the stored procedure:

CREATE PROCEDURE MySP AS
BEGIN
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   BEGIN TRAN 
       SELECT * FROM MyTable
       SELECT * FROM MyTable2
       SELECT * FROM MyTable3

       COMMIT TRAN  
END

Please suggest.

Answer 1

Both versions are "correct" - they just do different things:

• Your attempt #1 sets the isolation level for that database and connection - that means, the chosen isolation level will be used for any future statement - until you change the isolation level again

• Your attempt #2 sets the isolation level only INSIDE the stored procedure - so once the stored procedure is completed, the isolation level that existed on the database/connection level is restored again

So it really depends on what you want to do:

• set the isolation level to a different level in general for your current connection to this database? Any future statement will be run under this isolation level --> choose #1

• set the isolation level to a different setting for just a single stored procedure - regardless of what the connnection/database had before - then use #2