Sybase Technical Library - Product Manuals Home
[Search Forms] [Previous Section with Hits] [Next Section with Hits] [Clear Search] Expand Search
Transactions [Table of Contents] Wildcard Characters

SQL Server Reference Manual

[-] Chapter 5: Transact-SQL Topics
[-] Variables (Local and Global)

Variables (Local and Global)

The Two Kinds of Variables

Variables are defined entities that are assigned values. SQL Server has two kinds of variables:

Where to Use Local Variables

Variables can be used nearly anywhere the SQL syntax indicates that an expression can be used, such as char_expr, integer_expression, numeric_expr or float_expr. They can be passed as parameters to system procedures. Variables are often used in a batch or procedure as counters for while loops or if...else blocks.

Declaring a Local Variable

Use the following syntax to declare a local variable's name and datatype: 

declare @variable_name datatype 
[, @variable_name datatype]...

The variable name must be preceded by the @ sign and conform to the rules for identifiers. The datatype can be any datatype except text, image, or sysname.

Assigning a Value to a Local Variable

When a variable is declared, it has NULL value. Use a select statement to assign a specific value to the variable: 

select @variable_name = expression 
    [ , @variable_name = expression ]... 
[from clause] [where clause] [group by clause] 
[having clause] [order by clause] [compute clause]

The select statement that assigns values to local variables should return a single value. If it returns more than one value, the variable is assigned the last value returned. If it does not return a value (for example, if no rows are matched in a where clause) the value of the variable is left unchanged.

You cannot use the same select statement to assign a value to a variable and to retrieve data. For example, the following is not legal: 

/* ILLEGAL STATEMENT */ 
declare @veryhigh money 
select @veryhigh = max(price), title_id from titles

Do not use a single select statement to assign a value to one variable and then to another whose value is based on the first. Doing so can yield unpredictable results. For example, the following queries both try to find the value of @c2. The first query yields NULL, while the second query yields the correct answer, 0.033333:

/* this is wrong*/
declare @c1 float, @c2 float
select @c1 = 1000/1000, @c2 = @c1/30
select @c1, @c2
 
/* do it this way */ 
declare @c1 float, @c2 float
select @c1 = 1000/1000
select @c2 = @c1/30
select @c1 , @c2
  1. declare @veryhigh money
    select @veryhigh = max(price) from titles
    if @veryhigh > $20
    print "Ouch!"

  2. declare @one varchar(18), @two varchar(18)
    select @one = "this is one", @two = "this is two"
    if @one = "this is one"
    print "you got one"
    if @two = "this is two"
    print "you got two"
    else print "nope"

  3. /* Determine if a given au_id has a row in au_pix*/
    /* Turn off result counting */
    set nocount on
    /* declare the variables */
    declare @c int,
    @min_id varchar(30)
    /*First, count the rows*/
    select @c = count(*) from authors
    /* Initialize @min_id to "" */
    select @min_id = ""
    /* while loop executes once for each authors row */
    while @c > 0
    begin
    /*Find the smallest au_id*/
    select @min_id = min(au_id)
    from authors
    where au_id > @min_id
    /*Is there a match in au_pix?*/
    maxreadif exists (select au_id
    from au_pix
    where au_id = @min_id)
    begin
    print "A Match! %1!", @min_id
    end
    select @c = @c -1 /*decrement the counter */
    end

    This example uses local variables in a counter in a while loop, for doing matching in a where clause, in an if statement, and also sets or resets values in select statements.

Using Global Variables

Predefined global variables are distinguished from local variables by having two @ signs preceding their names, for example, @@error, @@rowcount. Users cannot create global variables, and cannot update the value of global variables directly in a select statement.

Many global variables report on system activity occurring from the last time SQL Server was started. The system procedure sp_monitor displays the current values of some of the global variables.

List of Global Variables

The global variables are:

Table 5-25: Global variables

Variable

Description

@@char_convert

Contains 0 if character set conversion is not in effect. Contains 1 if character set conversion is in effect.

@@client_csname

The client's character set name. Set to NULL if client character set has never been initialized; otherwise, it contains the name of the most recently used character set.

@@client_csid

The client's character set ID. Set to -1 if client character set has never been initialized; otherwise, it contains the most recently used client character set ID from syscharsets.

@@connections

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

@@cpu_busy

The amount of time, in ticks, that the CPU has spent doing SQL Server work since the last time SQL Server was started.

@@error

Commonly used to check the error status (succeeded or failed) of the most recently executed statement. It contains 0 if the previous transaction succeeded; otherwise, it contains the last error number generated by the system. A statement such as:

if @@error != 0 return

causes an exit if an error occurs.

Every Transact-SQL statement resets @@error, including print statements or if tests, so the status check must immediately follow the statement whose success is in question.

@@identity

The last value inserted into an IDENTITY column by an insert or select into statement. @@identity is reset each time a row is inserted into a table. If a statement inserts multiple rows, @@identity reflects the IDENTITY value for the last row inserted. If the affected table does not contain an IDENTITY column, @@identity is set to 0.

The value of @@identity is not affected by the failure of an insert or select into statement, or the rollback of the transaction that contained it. @@identity retains the last value inserted into an IDENTITY column, even if the statement that inserted it fails to commit.

@@idle

The amount of time, in ticks, that SQL Server has been idle since it was last started.

@@io_busy

The amount of time, in ticks, that SQL Server has spent doing input and output operations since it was last started.

@@isolation

The current isolation level of the Transact-SQL program. @@isolation takes the value of the active level (0, 1, or 3).

@@langid

The local language id of the language currently in use (specified in syslanguages.langid).

@@language

The name of the language currently in use (specified in syslanguages.name).

@@maxcharlen

The maximum length, in bytes, of a character in SQL Server's default character set.

@@max_connections

The maximum number of simultaneous connections that can be made with SQL Server in this computer environment. The user can configure SQL Server for any number of connections less than or equal to the value of @@max_connections with sp_configure ''number of user connections''.

@@ncharsize

The average length, in bytes, of a national character.

@@nestlevel

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

@@pack_received

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

@@pack_sent

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

@@packet_errors

The number of errors that have occurred while SQL Server was sending and receiving packets.

@@procid

The stored procedure ID of the currently executing procedure.

@@rowcount

The number of rows affected by the last command. @@rowcount is set to 0 by any command which does not return rows, such as an if statement. With cursors, @@rowcount represents the cumulative number of rows returned from the cursor result set to the client, up to the last fetch request.

@@servername

The name of the local SQL Server. You must define a server name with sp_addserver, and then restart SQL Server.

@@spid

The server process ID number of the current process.

@@sqlstatus

Contains status information resulting from the last fetch statement. @@sqlstatus may contain the following values:

0 - The fetch statement completed successfully.

1 - The fetch statement completed successfully.

2 - There is no more data in the result set. This warning occurs if the current cursor position is on the last row in the result set and the client submits a fetch command for that cursor.

@@textcolid

The ID of the column referenced by @@textptr. The datatype of this variable is tinyint.

@@textdbid

The database ID of a database containing an object with the column referenced by @@textptr. The datatype of this variable is smallint.

@@textobjid

The ID of the object containing the column referenced by @@textptr. The datatype of this variable is int.

@@textptr

The text pointer of the last text or image column inserted or updated by a process. The datatype of this variable is binary(16). (Do not confuse this variable with the textptr() function.)

@@textsize

The current value of the set textsize option, which specifies the maximum length, in bytes, of text or image data to be returned with a select statement. Defaults to 32K.

@@textts

The text time stamp of the column referenced by @@textptr. The datatype of this variable is varbinary(8).

@@thresh_hysteresis

The change in free space required to activate a threshold. This amount, also known as the hysteresis value, is measured in 2K database pages. It determines how closely thresholds can be placed on a database segment.

@@timeticks

The number of microseconds per tick. The amount of time per tick is machine dependent.

@@total_errors

The number of errors that have occurred while SQL Server was reading or writing.

@@total_read

The number of disk reads by SQL Server since it was last started.

@@total_write

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

@@tranchained

The current transaction mode of the Transact-SQL program. @@tranchained returns 0 for unchained or 1 for chained.

@@trancount

The nesting level of transactions. Each begin transaction in a batch increments the transaction count. When you query @@trancount in chained transaction mode, its value is never zero since the query automatically initiates a transaction.

@@transtate

The current state of a transaction after a statement executes. However, @@transtate does not get cleared for each statement, unlike @@error. @@transtate may contain the following values:

0 - Transaction in progress: an explicit or implicit transaction is in effect; the previous statement executed successfully.

1 - Transaction succeeded: the transaction completed and committed its changes.

2 - Statement aborted: the previous statement was aborted; no effect on the transaction.

3 - Transaction aborted: the transaction aborted and rolled back any changes.

@@version

The date of the current version of SQL Server.

See Also

Commands

declare, print, raiserror

Topics

System and User-Defined Datatypes

System procedures

sp_monitor

See Also [Table of Contents] Wildcard Characters