| Previous | Table of Contents | Next |
There are two types of global variables: server-specific and connection-specific. In Table 12.1, I’ve listed connection-specific variables.
| Variable Name | Returns |
|---|---|
| @@error | An integer indicating the type of error that occurred. If the value is not zero, an error occurred. For example, 1205 indicates that a deadlock error occurred on the last statement. |
| @@cursor_rows | Returns number of rows in last-opened cursor. A negative value indicates the cursor is being populated by a separate thread, and the value gives the current number of rows returned thus far. Cursors are explained on Day 14. |
| @@fetch_status | Returns status of last fetch operation against a cursor. Zero means that the fetch worked, -1 means that the fetch was before or after the set of rows, and -2 means that the row you tried to fetch has been deleted. |
| @@identity | Returns the value that was automatically inserted by the server when a table had a value inserted. This is set on insert only—@@identity isn’t set on update. This variable is extremely useful for retrieving key values after inserting values. Refer back to Day 9 for examples using @@identity. |
| @@nestlevel | The nesting level of stored procedures or triggers. Every time a new procedure is called from within a parent proc, the nest level is increased by one. A maximum of 16 procedures can be “active” at one time. If this is exceeded, the execution is aborted. Sometimes this leads to trouble if a trigger fires, calls a proc, and this proc causes triggers to fire on other tables. |
| @@rowcount | Returns the number of rows affected by the last statement. Flow control statements such as IF and WHILE reset rowcount to zero, so save rowcount in a local variable if you want to keep it around after the if test. (See the code in the next section, “Using Global Variables in SQL,” for an example.) |
| @@spid | The server process ID for this process. The SPID also appears in sp_who and sp_lock output, which is assembled from the master..sysprocesses and master..syslocks tables, respectively. |
| @@trancount | Transaction nesting level. Details on Day 13. |
Most server-specific global variables relate to statistics, as shown in Table 12.2. Any user can inquire these statistics by selecting the variable name. Each of the global variables described in Table 12.2 collects statistics since the last time the server was booted.
| These global variables collect statistics since the last time the server was booted. | |
|---|---|
| @@connections | Number of logins (successful and failed). |
| @@cpu_busy | Number of ticks spent doing work. 300 ticks to a second. |
| @@idle | Ticks spent not doing work. |
| @@io_busy | Ticks spent reading and writing data to disk. |
| @@pack_received | Packets received. |
| @@pack_sent | Packets sent. |
| @@packet_errors | Number of times an error occurred sending or receiving packets. |
| @@total_read | Total number of physical disk reads. |
| @@total_write | Total number of physical disk writes. |
Table 12.3 discusses other server-specific variables.
| @@max_precision | Maximum precision of decimal and numeric data types (precise decimals), normally 28 digits. If you require precise operations, you might write a procedure that first tests @@max_precision and prints an error message if the precision is too low. If the precision is up to snuff, continue with your processing. Higher precision is achievable by running the server executable with command-line options. |
| @@servername | The name of the server to which you are currently connected. This can be helpful in application programs when users might connect to a number of different servers. |
| @@version | Version information for the server. |
Using Global Variables in SQL
This example uses @@rowcount to verify that rows were deleted. The SQL code deletes authors who write for New Moon Books. (The IF test is covered in the “Flow Control” section.) If this were part of a stored proc, you might take an action other than printing an error message if rowcount were zero.
delete authors
where au_id in(
select au_id from titleauthor
where title_id in(
select title_id from titles
where pub_id in(
select pub_id from publishers
where pub_name = ‘New Moon Books’
)
)
)
if @@rowcount = 0
print ‘No rows deleted! Big trouble!’
Msg 547, Level 16, State 2
DELETE statement conflicted with COLUMN REFERENCE constraint
‘FK__titleauth__au_id__1312E04B’. The conflict occurred
in database ‘pubs’, table ‘titleauthor’, column ‘au_id’
Command has been aborted.
No rows deleted! Big trouble!
Oops! Before deleting the author from authors, which maintains a primary key on au_id, I need to remove related foreign keys. Because no rows were deleted, the IF test is true, and the message is printed. Constraint violations abort the command but do not abort the batch, so processing can continue with the PRINT statement.
| Previous | Table of Contents | Next |