SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in SP

• mrsiro

  Old Hand

  Points: 376

  More actions

  August 31, 2020 at 3:29 am

  #3784798

  Hello guys, have a nice day.

  What is better code:

  CREATE PROCEDURE SPtest
  @kind NVARCHAR(5)
  AS
   BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  
    IF @kind = '1'
    BEGIN
     SELECT * FROM dbo.table1
    END
  
    IF @kind = '2'
    BEGIN
     SELECT * FROM dbo.table2
    END
   END

  Or:

  CREATE PROCEDURE SPtest
  @kind NVARCHAR(5)
  AS
   BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    IF @kind = '1'
     BEGIN
      SELECT * FROM dbo.table1
     END
  
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    IF @kind = '2'
     BEGIN
      SELECT * FROM dbo.table2
     END
   END

  • This topic was modified 4 years, 2 months ago by  mrsiro.

• Erland Sommarskog

  SSC-Insane

  Points: 24038

  More actions

  August 31, 2020 at 6:35 am

  #3784814

  I'm tempted to say that none of them are good. If you really have reason to put this exceptional command in a stored procedure, you should add a comment that says why you are taking this gamble, and why it is acceptable.

  [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

• frederico_fonseca

  SSCoach

  Points: 16154

  More actions

  August 31, 2020 at 7:39 am

  #3784822

  neither is good - but having the single set statement just makes it easier for you to change your code when time comes your users complain about bad data