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:

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 entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s