Post reply

Scope of @@rowcount

  • October 12, 2010 at 2:09 am

    #143642

    whts the scope of @@rowcount

    for ex

    begin

    insert stmt

    if @@rowcount>0

    begin

    insert stmt

    if @@rowcount>0-- will it be affected by the first insert statement?

    do smthg

    end

    end

    any help?

  • October 12, 2010 at 2:12 am

    #1235336

    Yes. As in Yes it will get effected.

    /T

  • October 12, 2010 at 5:26 am

    #1235405

    but that shouldnt affect from first @@rowcount value..right?.. is thr any other way to make sure a rows are inserted or updated?

  • October 12, 2010 at 5:32 am

    #1235408

    every insert/update/select/set/delete statement resets the @@rowcount to the rows affected by the executed statement, so it's a best practice to use a variable to capture the rowcount you need,:

    begin

    declare @myrowcount int,

    @myrowcount2 int

    insert stmt

    SET @myrowcount=@@rowcount

    if @myrowcount>0

    begin

    insert stmt

    SET @myrowcount2 =@@rowcount

    if @myrowcount2 >0-- will it be affected by the first insert statement?

    do smthg

    end

    end

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 12, 2010 at 5:55 am

    #1235428

    Thanks Lowell!!!!

  • October 12, 2010 at 6:28 am

    #1235448

    Lowell (10/12/2010)

    every insert/update/select/set/delete statement resets the @@rowcount to the rows affected by the executed statement

    It's more general than that. It's just about every statement.

    SELECT * FROM master.sys.objects -- 74 rows

    IF (1=1)

    SELECT @@ROWCOUNT AS RowsAffected -- 0, because the IF did not affect any rows

    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

  • October 12, 2010 at 6:35 am

    #1235452

    GilaMonster (10/12/2010)

    It's more general than that. It's just about every statement.

    SELECT * FROM master.sys.objects -- 74 rows

    IF (1=1)

    SELECT @@ROWCOUNT AS RowsAffected -- 0, because the IF did not affect any rows

    wow i did not know that even an IF statement affects it! thanks once again Gail!

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 12, 2010 at 6:59 am

    #1235478

    Lowell (10/12/2010)

    GilaMonster (10/12/2010)

    It's more general than that. It's just about every statement.

    SELECT * FROM master.sys.objects -- 74 rows

    IF (1=1)

    SELECT @@ROWCOUNT AS RowsAffected -- 0, because the IF did not affect any rows

    wow i did not know that even an IF statement affects it! thanks once again Gail!

    I found that out after using this construct and getting really, really confused...

    Insert <stuff>

    IF @@Error = 0

    SET @RowCount = @@RowCount

    ELSE

    GOTO ErrorHandler

    @@Error behaves the same way.

    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

Viewing 8 posts - 1 through 7 (of 7 total)

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