564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Using @@rowcount and other global variables
Part one 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!
The first thing to know about global variables in SQL is that they aren't variables (for these, read my separate blog). 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.
Here's an example of the use of the main global variables in SQL:
-- insert a film
INSERT INTO tblFilm (FilmName) VALUES ('E.T.')
@@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.
|Parts of this blog|
25 Aytoun Street