SQL Server: how to set default isolation level for the entire stored procedure? SQL Server: how to set default isolation level for the entire stored procedure? sql sql

SQL Server: how to set default isolation level for the entire stored procedure?


First, the default isolation level in SQL Server is Read Committed, so that statement doesn't really do anything unless you've changed the default isolation level.

But, in general, yes, SET Transaction Isolation Level will change the isolation level for the whole procedure (the duration of the connection, in fact)

Keep in mind that all SQL statements are implicit transactions meaning that if, for example, an update fails 99% through, it will rollback automatically; no BEGIN TRAN/COMMIT is necessary.

To answer your question, yes, your SELECT statements will inherit the isolation level you set (or the default if you do not set one) unless you override the behavior with a query hint like WITH NOLOCK which will make the individual query behave as though you did SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED