Friday, December 11, 2009

Command to capture the number of rows affected by a Select or Update statement


Using @@Rowcount to Determine the Number of Rows Affected by a SQL Statement

The @@Rowcount function will be set after any statement that changes or returns rows. In the following statement, the RowsReturned column will display the number of rows selected by the previous select statement:

SELECT * FROM AUTHORS
WHERE state = 'CA'
SELECT @@rowcount AS 'RowsReturned'

While this example could obviously be rewritten to use a SELECT COUNT... type of syntax, the @@Rowcount function is useful in that it is also set after any statement that changes rows. As such it is useful in determining how many rows were affected by an INSERT or an UPDATE statement. For example, the following SQL statement changes the city column in the authors table of the pubs database from Salt Lake City to Oakland:

UPDATE authors SET city = 'Oakland' WHERE city = 'Salt Lake City'
SELECT @@rowcount AS 'RowsChanged'

The statement will return the number of rows changed as the RowsChanged column.

This can be used in PeopleCode inside SQLExec.

Eg.  SQLExec ("SELECT * FROM AUTHORS
WHERE state = 'CA'
SELECT @@rowcount AS 'RowsReturned'");

4 comments:

  1. Even without specifying "SELECT @@rowcount AS 'RowsReturned'", SQLExec function itself will return the no. of rows affected?

    ReplyDelete
  2. SQLEXEC will process only on 1 row rt

    ReplyDelete
  3. @Nadaraj: This will be helpful if you want to capture the number of rows effected and say for example writing it to a file if the client insist.

    ReplyDelete
  4. @Mridula: You are right!

    ReplyDelete

Total Pageviews

Copyright © Tech & Management Notes