Post reply

BEGIN TRAN with SELECT statements

  • January 21, 2015 at 1:32 am

    #293994

    Hello,

    Short question... Should BEGIN TRAN ...COMMIT be used in a procedure body if I have only select statements??

    OR

    Something like this it's ok?

    CREATE PROCEDURE [dbo].[procname]

    @param1int

    AS

    BEGIN

    SET NOCOUNT ON;

    BEGIN TRY

    SELECT

    col1

    ,col2

    FROM table

    WHERE @param1= col1

    END TRY

    BEGIN CATCH

    THROW;

    END CATCH

    END

  • January 21, 2015 at 1:36 am

    #1771699

    With a single select it's not necessary. With multiple selects, if you're running under the default isolation level then the transactions don't change anything about how the selects behave, in higher isolation levels (repeatable read, serializable, snapshot) they can change behaviour, so you need to think about what you need.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 21, 2015 at 1:46 am

    #1771702

    Yes the isolation level is default.

    Great.

    Thanks Gila

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply