Post reply

What Resets @@ROWCOUNT?

  • November 6, 2020 at 12:00 am

    #3801878

    Comments posted to this topic are about the item What Resets @@ROWCOUNT?

  • November 6, 2020 at 10:08 am

    #3804897

    I would've expected DROP TABLE to set @@ROWCOUNT to the number of dropped rows.

  • November 6, 2020 at 12:56 pm

    #3804943

    I beg to differ on the answers. The QOT basically said that all but the SET variable would reset @@ROWCOUNT to zero, however BOL states the following:

    • Statements that make a simple assignment always set the @@ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are: SET @local_variable, RETURN, READTEXT, and select without query statements such as SELECT GETDATE() or SELECT 'Generic Text'.
    • Statements such as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, PRINT, RAISERROR, BEGIN TRANSACTION, or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

    So, according to that, RETURN would set it to 1, not 0. Also, nothing was stated about creating or dropping a table.

    LinkedIn: https://www.linkedin.com/in/sqlrv
    Website: https://www.sqlrv.com

  • November 6, 2020 at 1:17 pm

    #3804947

    Aaron N. Cutshall wrote:

    I beg to differ on the answers. The QOT basically said that all but the SET variable would reset @@ROWCOUNT to zero, however BOL states the following:

     

      <li style="list-style-type: none;">

    • Statements that make a simple assignment always set the @@ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are: SET @local_variable, RETURN, READTEXT, and select without query statements such as SELECT GETDATE() or SELECT 'Generic Text'.

     

      <li style="list-style-type: none;">

    • Statements such as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, PRINT, RAISERROR, BEGIN TRANSACTION, or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

     

    So, according to that, RETURN would set it to 1, not 0. Also, nothing was stated about creating or dropping a table.

    There are two ways to use RETURN:

    RETURN <value>

    and

    RETURN

    The first will set @@ROWCOUNT to 1; the second will set it to 0.

    As for the CREATE TABLE and DROP TABLE statements, BOL does not mention them. So I tested them.

     

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

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