Using @@rowcount and other global variables
Part two of a two-part series of blogs

SQL comes complete with a few useful global variables, giving you information such as the row number of the record just inserted, or the number of rows affected by a query. Here's how to use them!

  1. Global Variables in SQL
  2. Getting the number of rows affected - @@ROWCOUNT (this blog)

This is part of our full online SQL tutorial blog.  However, you might also like to consider booking onto one of our SQL courses.

Posted by Andy Brown on 28 January 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Getting the number of rows affected - @@ROWCOUNT

If you've just selected, inserted, deleted or updated rows in SQL, @@rowcount will tell you how many.  However, you have to access it immediately!

@@ROWCOUNT keeps getting overwritten

Here are a couple of examples: one which will work, and one which won't.  Let's start with the one which works.  The following SQL takes all films winning 10 or more Oscars, and puts them in a new table:

-- put all of the films winning 10

-- or more Oscars into a new table

SELECT

FilmName,

FilmReleaseDate

INTO

DoubleDigitOscars

FROM

tblFilm

WHERE

FilmOscarWins >= 10

 

-- show how many rows were inserted

SELECT 'Created ' +

CAST(@@rowcount AS varchar(3)) +

' rows in new table'

Here's the output I got when I just ran this query:

Created 2 rows message

The query put two rows in the new table.

 

This works because I interrogated the row count immediately.  If you add in another statement, however, things go awry:

-- put all of the films winning 10

-- or more Oscars into a new table

SELECT

FilmName,

FilmReleaseDate

INTO

DoubleDigitOscars

FROM

tblFilm

WHERE

FilmOscarWins >= 10

 

-- any command here resets row count

PRINT 'Output follows'

 

-- show how many rows were inserted

SELECT 'Created ' +

CAST(@@rowcount AS varchar(3)) +

' rows in new table'

This would show:

No rows message

This time, the query would show that no rows had been added.

 

The reason is that @@rowcount holds the number of rows affected by the last executed statement - which in the above example is 0, because the PRINT command doesn't affect rows in a table!

Using a variable to store @@rowcount

An easy way round this is to store the number of rows affected, then use this subsequently:

-- will hold number of rows added

DECLARE @NumRowsAdded int

 

-- put all of the films winning 10

-- or more Oscars into a new table

SELECT

FilmName,

FilmReleaseDate

INTO

DoubleDigitOscars

FROM

tblFilm

WHERE

FilmOscarWins >= 10

 

-- store number rows inserted

SET @NumRowsAdded = @@ROWCOUNT

 

-- any command here resets row count

PRINT 'Output follows'

 

-- show how many rows were inserted

SELECT 'Created ' +

CAST(@NumRowsAdded AS varchar(3)) +

' rows in new table'

Here the use of the @NumRowsAdded variable gets round the problem.

  1. Global Variables in SQL
  2. Getting the number of rows affected - @@ROWCOUNT (this blog)
This blog has 0 threads Add post