Caché Transact-SQL (TSQL) Migration Guide
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 '
DECLARE @a CHAR(20),@b CHAR(20)
SELECT @a='hello ',@b='world!'
If declaring variables is inconvenient you can switch this check off using the NDC
setting. However, cursors must be declared, even if NDC
Stored procedure arguments are automatically declared as local variables.
Initial and Default Values
Declared variables are initialized to ""
) 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 ""
). This default is compatible with MS SQLServer; it is not compatible with Sybase.
@@ 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:
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.
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.
Contains the IDENTITY field value of the most recently inserted, updated, or deleted row.
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.
Contains an integer specifying the nesting level of the current process. The maximum is 16.
Contains the number of rows affected by the most recent SELECT
, 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.
Contains the Caché instance name.
Contains the server process ID of the current process.
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.
Contains the number of currently active transactions.
Contains the Caché version number and date and time of its installation.