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.
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