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 values 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 (for example, CURRENT DATE, CURRENT TIME, USER, and SQLSTATE) 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 VARCHAR(100) )
 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 SQL Anywhere. Some global variables are supplied for compatibility with Transact-SQL, and return a fixed value of either 0, 1, or NULL, as noted.

Variable name Meaning
@@char_convert 0 (Provided for compatibility with Transact-SQL.)
@@client_csid -1 (Provided for compatibility with Transact-SQL.)
@@client_csname NULL (Provided for compatibility with Transact-SQL.)
@@connections The number of logins since the server was last started.
@@cpu_busy 0 (Provided for compatibility with Transact-SQL.)
@@dbts A value of type TIMESTAMP representing the last generated value used for all columns defined with DEFAULT TIMESTAMP.
@@error

A Transact-SQL error code that checks the success or failure of the most recently executed statement. If the previous transaction succeeded, 0 is returned. If the previous transaction was unsuccessful, the last error number generated by the system is returned. To view descriptions of the values returned by @@error, see Error handling in Transact-SQL procedures.

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 you want verified.

@@fetch_status

Contains status information resulting from the last fetch statement. This feature is the same as @@sqlstatus, except that it returns different values. It is for Microsoft SQL Server compatibility. @@fetch_status 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.
@@identity Last value inserted into any IDENTITY or DEFAULT AUTOINCREMENT column by an INSERT or SELECT INTO statement. See @@identity global variable.
@@idle 0 (Provided for compatibility with Transact-SQL.)
@@io_busy 0 (Provided for compatibility with Transact-SQL.)
@@isolation Current isolation level of the connection. @@isolation takes the value of the active level.
@@langid Unique language ID for the language in use by the current connection.
@@language Name of the language in use by the connection.
@@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).
@@maxcharlen Maximum length, in bytes, of a character in the CHAR character set.
@@ncharsize Maximum length, in bytes, of a character in the NCHAR character set.
@@nestlevel -1 (Provided for compatibility with Transact-SQL.)
@@pack_received 0 (Provided for compatibility with Transact-SQL.)
@@pack_sent 0 (Provided for compatibility with Transact-SQL.)
@@packet_errors 0 (Provided for compatibility with Transact-SQL.)
@@procid Stored procedure ID of the currently executing procedure.
@@rowcount

Number of rows affected by the last statement. The value of @@rowcount should be checked immediately after the 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.

The @@rowcount is not reset to zero by any statement which does not affect rows, such as an IF statement.
@@servername Name of the current database server.
@@spid 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 0 (Provided for compatibility with Transact-SQL.)
@@timeticks 0 (Provided for compatibility with Transact-SQL.)
@@total_errors 0 (Provided for compatibility with Transact-SQL.)
@@total_read 0 (Provided for compatibility with Transact-SQL.)
@@total_write 0 (Provided for compatibility with Transact-SQL.)
@@tranchained Current transaction mode; 0 for unchained or 1 for chained.
@@trancount Nesting level of transactions. Each BEGIN TRANSACTION in a batch increments the transaction count.
@@transtate -1 (Provided for compatibility with Transact-SQL.)
@@version Version number of the current version of SQL Anywhere.

@@identity global variable

Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1