Home|Management Portal|Index
Caché Transact-SQL (TSQL) Migration Guide
TSQL Variables
« »
   
Server:docs.intersystems.com
Instance:CACHE20102
User:UnknownUser
 
-
Go to:
Search:    

Host Variables and @ Local Variables
Standard host variables and literal numbers and strings (including multiline strings) are supported.
TSQL local variables are specified using an at sign (@) prefix. For example, @a or @myvar. TSQL special (system-defined) variables are identified by an @@ prefix. For Example, @@ROWCOUNT. A variable name must be a valid identifier.
A local variable must be declared (using DECLARE or as a formal parameter) before use. The declaration must specify a data type, though strict data typing is not enforced in Caché TSQL. For a list of supported data types, refer to the TSQL Constructs chapter of this document.
A local variable can be set using either the SET command or the SELECT command. The following examples show two local variables being declared, set, and displayed:
DECLARE @a CHAR(20),@b CHAR(20)
SET @a='hello '
SET @b='world!'
PRINT @a,@b
DECLARE @a CHAR(20),@b CHAR(20)
SELECT @a='hello ',@b='world!'
PRINT @a,@b
If declaring variables is inconvenient you can switch this check off using the NDC setting. However, cursors must be declared, even if NDC is used.
Stored procedure arguments are automatically declared as local variables.
Initial and Default Values
Declared variables are initialized to "" (SQL NULL) at the start of the procedure.
If a declared variable is set to the results of a scalar subquery, and the subquery returns no rows, Caché TSQL sets the variable to "" (SQL NULL). This default is compatible with MS SQLServer; it is not compatible with Sybase.
@@ Special Variables
@@ variables are system-defined; they cannot be created or modified by user processes. @@ variables are global in scope (available to all processes). They are thus sometimes referred to elsewhere in the Transact-SQL literature as “global variables.” Because the term “global variable” is used widely in Caché and differs significantly in meaning, these TSQL @@ variables are referred to here as “special variables” to avoid confusion.
The following special variables are implemented. Invoking an unimplemented special variable generates a #5001 '@@nnn' unresolved symbol error or a #5002 <UNDEFINED> error. The corresponding Caché ObjectScript (COS) and Caché SQL generated code for each special variable is provided:
@@ERROR
Contains the error number of the most recent TSQL error. 0 indicates that no error has occurred. A 0 value is returned when either SQLCODE=0 (successful completion) or SQLCODE=100 (no data, or no more data). To differentiate these two results, use @@SQLSTATUS.
COS SQLCODE
SQL :SQLCODE
@@FETCH_STATUS
Contains an integer specifying the status of the last FETCH cursor statement. The available options are: 0=row successfully fetched; –1=no data could be fetched; –2 row fetched is missing or some other error occurred. A value of –1 can indicate that there is no data to FETCH, or that the fetch has reached the end of the data.
COS $Case($Get(SQLCODE,0),0:0,100:-1,:-2)
SQL CASE :SQLCODE WHEN 0 THEN 0 WHEN 100 THEN –1 ELSE –2 END
@@IDENTITY
Contains the IDENTITY field value of the most recently inserted, updated, or deleted row.
COS %ROWID
SQL :%ROWID
@@LOCK_TIMEOUT
Contains an integer specifying the timeout value for locks, in seconds. Lock timeout is used when a resource needs to be exclusively locked for inserts, updates, deletes, and selects. The default is 10.
COS LOCK command
SQL SET OPTION LOCK_TIMEOUT
@@NESTLEVEL
Contains an integer specifying the nesting level of the current process. The maximum is 16.
COS $STACK
@@ROWCOUNT
Contains the number of rows affected by the most recent SELECT, INSERT, UPDATE, or DELETE command. A single-row SELECT always returns a @@ROWCOUNT value of either 0 (no row selected) or 1.
When invoking an AFTER statement level trigger, the @@ROWCOUNT value upon entering the trigger is the @@ROWCOUNT immediately prior to the trigger. Rows affected within the scope of the trigger code are reflected in the @@ROWCOUNT value. Upon completion of the trigger code, @@ROWCOUNT reverts to the value immediately prior to the trigger invocation.
COS %ROWCOUNT
SQL :%ROWCOUNT
@@SERVERNAME
Contains the Caché instance name.
COS $PIECE($system,":",2)
@@SPID
Contains the server process ID of the current process.
COS $JOB
SQL $TSQL_SPID()
@@SQLSTATUS
Contains an integer specifying the completion status of the most recent SQL statement. Available values are: 0=successful completion; 1=failure; 2=no (more) data available.
COS $Case($Get(SQLCODE,0),0:0,100:2,:1)
SQL CASE :SQLCODE WHEN 0 THEN 0 WHEN 100 THEN 2 ELSE 1 END
@@TRANCOUNT
Contains the number of currently active transactions.
COS $TLEVEL
SQL $TSQL_TRANCOUNT()
@@VERSION
Contains the Caché version number and date and time of its installation.
COS $ZVERSION