This page has 0 threads | Add post

Global Variables in SQL

Written by Andy Brown

In this tutorial

Example of Global Variables
@@ROWCOUNT keeps getting overwritten
Using a variable to store @@rowcount

The first thing to know about global variables in SQL is that they aren't variables (for these, read my separate tutorial). Instead, they are useful bits of infomation made available by SQL Server, which you can use.

All global variables start with @@; all normal variables start with @.

The most useful global variables are the following:

Global variable	What it holds / notes
@@IDENTITY	Holds the id number of the last record added. I've covered the use of this in a separate blog (which explains that SCOPE_IDENTITY() is often a better alternative).
@@ROWCOUNT	Holds the number of rows affected by an SQL command - covered in the next part of this blog.
@@ERROR	The last error number generated by SQL (or 0 if there isn't one). I'll be covering this in a separate blog on error-handling and error messages, and will link to this when it's complete.
@@SERVERNAME	The name of the current SQL Server.
@@TRANCOUNT	The number of currently open transactions (covered in a later separate blog).

You can see a full list of all of the global variables in SQL here.

Example of Global Variables

Here's an example of the use of the main global variables in SQL:

-- insert a film

INSERT INTO tblFilm (FilmName) VALUES ('E.T.')

SELECT

@@SERVERNAME AS 'Server name',

@@ROWCOUNT AS 'Row count',

@@IDENTITY AS 'Id field value',

@@ERROR AS 'Error message',

@@TRANCOUNT AS 'Open transactions'

And here's what this could show:

5 global variable values for this query.

Now let's look in more detail at one particular global variable: @@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:

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:

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.

Previous
tutorial

CTEs and derived tables

Previous
part

Writing Loops in SQL

Top of page

Next
part

Next
tutorial

Trapping errors and debugging

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Programming in SQL

Global Variables in SQL

Example of Global Variables

@@ROWCOUNT keeps getting overwritten

Using a variable to store @@rowcount

Head office

London

Manchester