Variables

Variables are user-defined entities that are assigned values. A local variable is defined with a DECLARE statement, assigned an initial value with a SELECT statement, and used within the statement batch or procedure in which it was declared. Global variables are predefined and maintained by the system.

The following sections describe the declaration of local variables as well as the use of local and global variables.

Local Variables

Local variables are declared in the body of a batch or procedure with the DECLARE statement and given values with a SELECT statement. Local variables are often used in a batch or procedure as counters for WHILE loops or for IF...ELSE blocks.

Syntax

    Variable declaration:

DECLARE @variable_name datatype
    [, @variable_name datatype...]

    Variable assignment:

SELECT @variable = {expression | select_statement}
    [, @variable = {expression | select_statement}...]
[FROM table_list]
[WHERE search_conditions]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause]

where

@variable_name

Is the name of a variable. Variable names must be preceded by the "at" symbol (@). The single @ preceding the alphanumeric name signifies a local variable name. The variable name must conform to the rules for identifiers except that it can contain only 29 characters. Variables can be used only in place of constants. In standard SQL statements, variables cannot be used in place of the names of tables, columns, other database objects, or keywords. In SQL Server 6.0, strings can be resolved at execution time (rather than at compile time) by using the EXECUTE statement. For more information, see the EXECUTE statement.

datatype

Is a system or user-defined datatype. A variable cannot be of the image datatype. For more information on system datatypes, see the Datatypes topic. For more information about user-defined datatypes, see the sp_addtype system stored procedure.

Remarks

Local variables are often used in batches, stored procedures, and control-of-flow statement blocks. Local variables can be used only within the batch or procedure in which they are declared. When used in stored procedures, local variables are declared for automatic, non-interactive use by the procedure when it executes.

The SELECT statement that assigns a value to the local variable usually returns a single value. If the SELECT variable assignment statement returns more than one value, the variable is assigned the last value returned. If the SELECT variable assignment statement returns no rows, the variable retains its present value unless the variable assignment is made with a subquery. Only if the subquery returns no rows is the variable set to NULL. The SELECT statement that assigns values to variables cannot retrieve data in the same statement.

Global Variables

Predefined global variables can be used without being declared. Global variables are distinguished from local variables by having two "at" symbols (@@) preceding their names. Many of the global variables report on system activity since the last time SQL Server was started; others report information about a connection. In the listing that follows, connection-specific variables are noted with an asterisk (*). For information on the contents of a connection-specific variable, execute SELECT @@variablename. For the contents of many of these global variables, execute the sp_monitor system stored procedure.

@@CONNECTIONS

Specifies the number of logins or attempted logins since SQL Server was last started.

@@CPU_BUSY

Specifies the amount of time, in ticks (one three-hundredth second, or 3.33 milliseconds), that the CPU has spent doing SQL Server work since the last time SQL Server was started.

@@CURSOR_ROWS*

Specifies the number of qualifying rows in the last-opened cursor. @@CURSOR_ROWS returns:

-m	If the cursor is being populated asynchronously. The value returned (-m) refers to the number of rows currently in the keyset.
n	If the cursor is fully populated. The value returned (n) refers to the number of rows.
0	If no cursors have been opened or the last opened cursor has been closed or deallocated.

@@DATEFIRST

Returns the current value of the SET DATEFIRST parameter. Indicates the first day of each week: 1 for Monday, 2 for Tuesday, and so on through for Sunday.

@@DBTS

Specifies the value of the current timestamp datatype for the database. This timestamp is guaranteed to be unique for the database.

@@ERROR*

Specifies the last error number generated by the system for the user connection. The @@ERROR global variable is commonly used to check the error status (succeed or fail) of the most recently executed statement. It contains 0 if the last statement succeeded. Using @@ERROR with control-of-flow statements is advantageous for handling errors. The statement IF @@ERROR < > 0 RETURN exits if an error is returned.

@@FETCH_STATUS*

Contains the status of a cursor FETCH command. It is set to 0 if the fetch is successful, to - 1 if the fetch failed or the row was beyond results set, - 2 if the row fetched is missing.

@@IDENTITY*

Saves the last-inserted IDENTITY value. The @@IDENTITY variable is updated specifically for each user when an INSERT or SELECT INTO statement or bulk copy inserting into a table occurs. If a statement changes the table without an identity column, @@IDENTITY is set to NULL. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails or if the transaction is rolled back. For more information, see the IDENTITY property.

@@IDLE

Specifies the amount of time, in ticks (one three-hundredth second, or 3.33 milliseconds), that SQL Server has been idle since it was last started.

@@IO_BUSY

Specifies the amount of time, in ticks (one three-hundredth second, or 3.33 milliseconds), that SQL Server has spent doing input and output operations since it was last started.

@@LANGID*

Specifies the local language ID of the language currently in use (specified in syslanguages.langid).

@@LANGUAGE*

Specifies the language currently in use (specified in syslanguages.name).

@@MAX_CONNECTIONS

Specifies the maximum number of simultaneous connections that can be made with SQL Server in the current computer environment. The user can configure SQL Server for fewer connections by using the sp_configure system stored procedure. @@MAX_CONNECTIONS is not necessarily the number configured currently.

@@MAX_PRECISION

Returns the level of precision used by decimal and numeric datatypes as currently set in the server. By default, the maximum precision is 28; however, a larger precision can be set when SQL Server starts by using the /p parameter with sqlservr. For more information, see the Utilities and Executables topic.

@@MICROSOFTVERSION

Is a version used internally to track the current version of the server. If version checking is necessary, use @@VERSION.

@@NESTLEVEL*

Specifies the nesting level of the current execution (initially 0). Each time a stored procedure calls another stored procedure, the nesting level is incremented. If the maximum of 16 is exceeded, the transaction is terminated.

@@PACK_RECEIVED

Specifies the number of input packets read by SQL Server since it was last started.

@@PACK_SENT

Specifies the number of output packets written by SQL Server since it was last started.

@@PACKET_ERRORS

Specifies the number of errors that have occurred while SQL Server was sending and receiving packets since the last time SQL Server was started.

@@PROCID*

Specifies the stored procedure ID of the currently executing procedure.

@@REMSERVER*

Returns the server name contained within a remote server's login record.

@@ROWCOUNT*

Specifies the number of rows affected by the last statement. This variable is set to 0 by any statement that does not return rows, such as an IF statement.

@@SERVERNAME

Specifies the name of the local SQL Server. You must define this name with the sp_addserver system stored procedure, and then restart SQL Server. The setup program sets this variable to the computer name during installation. Although you can change @@SERVERNAME by using the sp_addserver system stored procedure and restarting SQL Server, this method is not usually required.

@@SERVICENAME

Specifies the name of a running service. Currently, @@SERVICENAME defaults to @@SERVERNAME.

@@SPID*

Specifies the server process ID number of the current process (the spid column of the sysprocesses system table).

@@TEXTSIZE*

Specifies the current value of the TEXTSIZE option of the SET statement, which specifies the maximum length, in bytes, of text or image data that a SELECT statement returns. The default limit is 4K.

@@TIMETICKS

Specifies the number of microseconds per tick. The amount of time per tick is computer-dependent. Each tick on the operating system is 31.25 milliseconds (1/32 second).

@@TOTAL_ERRORS

Specifies the number of errors that have occurred while SQL Server was reading or writing since the last time SQL Server was started.

@@TOTAL_READ

Specifies the number of disk reads by SQL Server since it was last started (disk reads only, not cache reads).

@@TOTAL_WRITE

Specifies the number of disk writes by SQL Server since it was last started.

@@TRANCOUNT*

Specifies the number of currently active transactions for the current user.

@@VERSION

Specifies the date, version number, and processor type for the current version of SQL Server.

See Also