SQL variables

Reference Manual
CHAPTER 6. SQL Language Elements

SQL variables

Adaptive Server Anywhere supports three levels of variables:

Local variables These are defined inside a compound statement in a procedure or batch using the DECLARE statement. They exist only inside the compound statement.
Connection-level variables These are defined with a CREATE VARIABLE statement. They belong to the current connection, and disappear when you disconnect from the database or when you use the DROP VARIABLE statement.
Global variables These are system-supplied variables that have system-supplied values.

Local and connection-level variables are declared by the user, and can be used in procedures or in batches of SQL statements to hold information. Global variables are system-supplied variables that provide system-supplied values. All global variables have names beginning with two @ signs. For example, the global variable @@version has a value that is the current version number of the database server. Users cannot define global variables.

Local variables

Local variables are declared using the DECLARE statement, which can be used only within a compound statement (that is, bracketed by the BEGIN and END keywords). The variable is initially set as NULL. The value of the variable can be set using the SET statement, or can be assigned using a SELECT statement with an INTO clause.

The syntax of the DECLARE statement is as follows:

DECLARE variable-name data-type

Local variables can be passed as arguments to procedures, as long as the procedure is called from within the compound statement.

Examples

The following batch illustrates the use of local variables.
```
BEGIN
   DECLARE local_var INT ;
   SET local_var = 10 ;
   MESSAGE 'local_var = ', local_var ;
END
```
Running this batch from Interactive SQL gives the message local_var = 10 on the server window.

The variable local_var does not exist outside the compound statement in which it is declared. The following batch is invalid, and gives a column not found error.

-- This batch is invalid.
BEGIN
   DECLARE local_var INT ;
   SET local_var = 10 ;
   MESSAGE 'local_var = ', local_var ;
END;
MESSAGE 'local_var = ', local_var ;

The following example illustrates the use of SELECT with an INTO clause to set the value of a local variable:
```
BEGIN
   DECLARE local_var INT ;
   SELECT 10 INTO local_var ;
   MESSAGE 'local_var = ', local_var ;
END
```
Running this batch from Interactive SQL gives the message local_var = 10 on the server window.

Compatibility

Names Adaptive Server Enterprise and Adaptive Server Anywhere both support local variables. In Adaptive Server Enterprise, all variables must be prefixed with an @ sign. In Adaptive Server Anywhere, the @ prefix is optional. To write compatible SQL, prefix all of your variables with @.
Scope The scope of local variables is different in Adaptive Server Anywhere and Adaptive Server Enterprise. Adaptive Server Anywhere supports the use of the DECLARE statement to declare local variables within a batch. However, if the DECLARE is executed within a compound statement, the scope is limited to the compound statement.
Declaration Only one variable can be declared for each DECLARE statement in Adaptive Server Anywhere. In Adaptive Server Enterprise, more than one variable can be declared in a single statement.

For more information on batches and local variable scope, see Variables and cursors.

Connection-level variables

Connection-level variables are declared with the CREATE VARIABLE statement. The CREATE VARIABLE statement can be used anywhere except inside compound statements. Connection-level variables can be passed as parameters to procedures.

The syntax for the CREATE VARIABLE statement is as follows:

CREATE VARIABLE variable-name data-type

When a variable is created, it is initially set to NULL. The value of connection-level variables can be set in the same way as local variables, using the SET statement or using a SELECT statement with an INTO clause.

Connection-level variables exist until the connection is terminated, or until the variable is explicitly dropped using the DROP VARIABLE statement. The following statement drops the variable con_var:

DROP VARIABLE con_var

Example

The following batch of SQL statements illustrates the use of connection-level variables.
```
CREATE VARIABLE con_var INT;
SET con_var = 10;
MESSAGE 'con_var = ', con_var;
```
Running this batch from Interactive SQL gives the message local_var = 10 on the server window.

Compatibility

Adaptive Server Enterprise does not support connection-level variables.

Global variables

Global variables have values set by the database server. For example, the global variable @@version has a value that is the current version number of the database server.

Global variables are distinguished from local and connection-level variables by having two @ signs preceding their names. For example, @@error and @@rowcount are global variables. Users cannot create global variables, and cannot update the value of global variables directly.

Some global variables, such as @@identity, hold connection-specific information, and so have connection-specific values. Other variables, such as @@connections, have values that are common to all connections.

Global variable and special constants

The special constants (CURRENT DATE, CURRENT TIME, USER, SQLSTATE and so on) are similar to global variables.

The following statement retrieves a value of the version global variable.

SELECT @@version

In procedures and triggers, global variables can be selected into a variable list. The following procedure returns the server version number in the ver parameter.

CREATE PROCEDURE VersionProc ( OUT ver
            NUMERIC ( 5, 2 ) )
BEGIN
   SELECT @@version
   INTO ver;
END

In Embedded SQL, global variables can be selected into a host variable list.

List of global variables

The following table lists the global variables available in Adaptive Server Anywhere

Variable name

Meaning

@@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 SQL statement resets @@error, so the status check must immediately follow the statement whose success is in question.

@@identity

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.

@@isolation

Current isolation level. @@isolation takes the value of the active level.

@@procid

Stored procedure ID of the currently executing procedure.

@@rowcount

Number of rows affected by the last statement. @@rowcount is set to zero by any statement which does not affect rows, such as an IF statement. Inserts, updates, and deletes set @@rowcount to the number of rows affected.

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

Name of the current database server.

@@sqlstatus

Contains status information resulting from the last fetch statement.

@@version

Version number of the current version of Adaptive Server Anywhere.

Variable name	Meaning
@@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 SQL statement resets @@error, so the status check must immediately follow the statement whose success is in question.
@@identity	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.
@@isolation	Current isolation level. @@isolation takes the value of the active level.
@@procid	Stored procedure ID of the currently executing procedure.
@@rowcount	Number of rows affected by the last statement. @@rowcount is set to zero by any statement which does not affect rows, such as an IF statement. Inserts, updates, and deletes set @@rowcount to the number of rows affected. 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	Name of the current database server.
@@sqlstatus	Contains status information resulting from the last fetch statement.
@@version	Version number of the current version of Adaptive Server Anywhere.

Compatibility

The following list includes all Adaptive Server Enterprise global variables supported in Adaptive Server Anywhere. Adaptive Server Enterprise global variables not supported by Adaptive Server Anywhere are not included in the list. In contrast to the above table, this list includes all global variables that return a value, including those for which the value is fixed at NULL, 1, -1, or 0, and may not be meaningful.

Global variable

Returns

@@char_convert

Returns 0

@@client_csname

In Adaptive Server Enterprise, 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. Returns NULL in Adaptive Server Anywhere

@@client_csid

In Adaptive Server Enterprise, 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. Returns -1 in Adaptive Server Anywhere

@@connections

The number of logins since the server was last started

@@cpu_busy

In Adaptive Server Enterprise, the amount of time, in ticks, that the CPU has spent doing Adaptive Server Enterprise work since the last time Adaptive Server Enterprise was started. In Adaptive Server Anywhere, returns 0

@@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 statement resets @@error, including PRINT statements or IF tests, so the status check must immediately follow the statement whose success is in question

@@identity

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

In Adaptive Server Enterprise, the amount of time, in ticks, that Adaptive Server Enterprise has been idle since it was last started. In Adaptive Server Anywhere, returns 0

@@io_busy

In Adaptive Server Enterprise, the amount of time, in ticks, that Adaptive Server Enterprise has spent doing input and output operations since it was last started. In Adaptive Server Anywhere, returns 0

@@isolation

Current isolation level of the connection. In Adaptive Server Enterprise, @@isolation takes the value of the active level

@@langid

In Adaptive Server Enterprise, defines the local language ID of the language currently in use. In Adaptive Server Anywhere, returns 0

@@language

In Adaptive Server Enterprise, defines the name of the language currently in use. In Adaptive Server Anywhere, returns "English"

@@maxcharlen

In Adaptive Server Enterprise, maximum length, in bytes, of a character in Adaptive Server Enterprise's default character set. In Adaptive Server Anywhere, returns 1

@@max_
connections

For the personal server, the maximum number of simultaneous connections that can be made to the server, which is 10

For the network server, the maximum number of active clients (not database connections, as each client can support multiple connections)

For Adaptive Server Enterprise, the maximum number of connections to the server

@@ncharsize

In Adaptive Server Enterprise, average length, in bytes, of a national character. In Adaptive Server Anywhere, returns 1

@@nestlevel

In Adaptive Server Enterprise, 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. In Adaptive Server Anywhere, returns -1

@@pack_received

In Adaptive Server Enterprise, number of input packets read by Adaptive Server Enterprise since it was last started. In Adaptive Server Anywhere, returns 0

@@pack_sent

In Adaptive Server Enterprise, number of output packets written by Adaptive Server Enterprise since it was last started. In Adaptive Server Anywhere, returns 0

@@packet_errors

In Adaptive Server Enterprise, number of errors that have occurred while Adaptive Server Enterprise was sending and receiving packets. In Adaptive Server Anywhere, returns 0

@@procid

Stored procedure ID of the currently executing procedure

@@rowcount

Number of rows affected by the last command. @@rowcount is set to zero 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

Name of the local Adaptive Server Enterprise or Adaptive Server Anywhere server

@@spid

In Adaptive Server Enterprise, server process ID number of the current process. In Adaptive Server Anywhere, the connection handle for the current connection. This is the same value as that displayed by the sa_conn_info procedure

@@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 resulted in an error

2 There is no more data in the result set

@@textsize

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. The default setting is 32765, which is the largest bytestring that can be returned using READTEXT. The value can be set using the SET statement

@@thresh_hysteresis

In Adaptive Server Enterprise, change in free space required to activate a threshold. In Adaptive Server Anywhere, returns 0

@@timeticks

In Adaptive Server Enterprise, number of microseconds per tick. The amount of time per tick is machine-dependent. In Adaptive Server Anywhere, returns 0

@@total_errors

In Adaptive Server Enterprise, number of errors that have occurred while Adaptive Server Enterprise was reading or writing. In Adaptive Server Anywhere, returns 0.

@@total_read

In Adaptive Server Enterprise, number of disk reads by Adaptive Server Enterprise since it was last started. In Adaptive Server Anywhere, returns 0

@@total_write

In Adaptive Server Enterprise, number of disk writes by Adaptive Server Enterprise since it was last started. In Adaptive Server Anywhere, returns 0

@@tranchained

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

@@trancount

Nesting level of transactions. Each BEGIN TRANSACTION in a batch increments the transaction count

@@transtate

In Adaptive Server Enterprise, current state of a transaction after a statement executes. In Adaptive Server Anywhere, returns -1

@@version

Information on the current version of Adaptive Server Enterprise or Adaptive Server Anywhere

Global variable	Returns
@@char_convert	Returns 0
@@client_csname	In Adaptive Server Enterprise, 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. Returns NULL in Adaptive Server Anywhere
@@client_csid	In Adaptive Server Enterprise, 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. Returns -1 in Adaptive Server Anywhere
@@connections	The number of logins since the server was last started
@@cpu_busy	In Adaptive Server Enterprise, the amount of time, in ticks, that the CPU has spent doing Adaptive Server Enterprise work since the last time Adaptive Server Enterprise was started. In Adaptive Server Anywhere, returns 0
@@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 statement resets @@error, including PRINT statements or IF tests, so the status check must immediately follow the statement whose success is in question
@@identity	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	In Adaptive Server Enterprise, the amount of time, in ticks, that Adaptive Server Enterprise has been idle since it was last started. In Adaptive Server Anywhere, returns 0
@@io_busy	In Adaptive Server Enterprise, the amount of time, in ticks, that Adaptive Server Enterprise has spent doing input and output operations since it was last started. In Adaptive Server Anywhere, returns 0
@@isolation	Current isolation level of the connection. In Adaptive Server Enterprise, @@isolation takes the value of the active level
@@langid	In Adaptive Server Enterprise, defines the local language ID of the language currently in use. In Adaptive Server Anywhere, returns 0
@@language	In Adaptive Server Enterprise, defines the name of the language currently in use. In Adaptive Server Anywhere, returns "English"
@@maxcharlen	In Adaptive Server Enterprise, maximum length, in bytes, of a character in Adaptive Server Enterprise's default character set. In Adaptive Server Anywhere, returns 1
@@max_ connections	For the personal server, the maximum number of simultaneous connections that can be made to the server, which is 10 For the network server, the maximum number of active clients (not database connections, as each client can support multiple connections) For Adaptive Server Enterprise, the maximum number of connections to the server
@@ncharsize	In Adaptive Server Enterprise, average length, in bytes, of a national character. In Adaptive Server Anywhere, returns 1
@@nestlevel	In Adaptive Server Enterprise, 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. In Adaptive Server Anywhere, returns -1
@@pack_received	In Adaptive Server Enterprise, number of input packets read by Adaptive Server Enterprise since it was last started. In Adaptive Server Anywhere, returns 0
@@pack_sent	In Adaptive Server Enterprise, number of output packets written by Adaptive Server Enterprise since it was last started. In Adaptive Server Anywhere, returns 0
@@packet_errors	In Adaptive Server Enterprise, number of errors that have occurred while Adaptive Server Enterprise was sending and receiving packets. In Adaptive Server Anywhere, returns 0
@@procid	Stored procedure ID of the currently executing procedure
@@rowcount	Number of rows affected by the last command. @@rowcount is set to zero 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	Name of the local Adaptive Server Enterprise or Adaptive Server Anywhere server
@@spid	In Adaptive Server Enterprise, server process ID number of the current process. In Adaptive Server Anywhere, the connection handle for the current connection. This is the same value as that displayed by the sa_conn_info procedure
@@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 resulted in an error 2 There is no more data in the result set
@@textsize	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. The default setting is 32765, which is the largest bytestring that can be returned using READTEXT. The value can be set using the SET statement
@@thresh_hysteresis	In Adaptive Server Enterprise, change in free space required to activate a threshold. In Adaptive Server Anywhere, returns 0
@@timeticks	In Adaptive Server Enterprise, number of microseconds per tick. The amount of time per tick is machine-dependent. In Adaptive Server Anywhere, returns 0
@@total_errors	In Adaptive Server Enterprise, number of errors that have occurred while Adaptive Server Enterprise was reading or writing. In Adaptive Server Anywhere, returns 0.
@@total_read	In Adaptive Server Enterprise, number of disk reads by Adaptive Server Enterprise since it was last started. In Adaptive Server Anywhere, returns 0
@@total_write	In Adaptive Server Enterprise, number of disk writes by Adaptive Server Enterprise since it was last started. In Adaptive Server Anywhere, returns 0
@@tranchained	Current transaction mode of the Transact-SQL program. @@tranchained returns 0 for unchained or 1 for chained
@@trancount	Nesting level of transactions. Each BEGIN TRANSACTION in a batch increments the transaction count
@@transtate	In Adaptive Server Enterprise, current state of a transaction after a statement executes. In Adaptive Server Anywhere, returns -1
@@version	Information on the current version of Adaptive Server Enterprise or Adaptive Server Anywhere