Skip to main content

TSQL Variables

Local Variables

By default, TSQL local variables are specified using an at sign (@) prefix, for example, @myvar. You can override this default to also allow PLAINLOCALS, TSQL local variables specified without an at sign (@) prefix. For example, myvar.

Declaring a Local Variable

A local variable must be declared (using DECLARE or as a formal parameter) before use. A variable name must be a valid identifier. Local variable names are not case-sensitive. The declaration must specify a data type, though strict data typing is not enforced in InterSystems TSQL. For a list of supported data types, see TSQL Constructs.

The DECLARE command has the following syntax:

DECLARE @var [AS] datatype [ = initval]

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.

Setting a Local Variable

A local variable can be set using either the SET command or the SELECT command. A local variable can be displayed using either the PRINT command or the SELECT command. The following Dynamic SQL examples show two local variables being declared, set, and displayed:

  SET myquery = 3
  SET myquery(1) = "DECLARE @a CHAR(20),@b CHAR(20) "
  SET myquery(2) = "SET @a='hello ' SET @b='world!' "
  SET myquery(3) = "PRINT @a,@b"
  SET tStatement = ##class(%SQL.Statement).%New(,,"MSSQL")
  SET qStatus = tStatement.%Prepare(.myquery)
   IF qStatus'=1 { WRITE "%Prepare failed",$System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  SET myquery = 3
  SET myquery(1) = "DECLARE @a CHAR(20),@b CHAR(20) "
  SET myquery(2) = "SELECT @a='hello ', @b='world!'"
  SET myquery(3) = "SELECT @a,@b"
  SET tStatement = ##class(%SQL.Statement).%New(,,"MSSQL")
  SET qStatus = tStatement.%Prepare(.myquery)
   IF qStatus'=1 { WRITE "%Prepare failed",$System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()

Initial and Default Values

By default, DECLARE initializes local variables to "" (SQL NULL). Optionally, you can specify an initial value (initval) for a local variable in the DECLARE command.

If a declared variable is set to the results of a scalar subquery, and the subquery returns no rows, InterSystems TSQL sets the variable to "" (SQL NULL). This default is compatible with MS SQLServer; it is not compatible with Sybase.

Plain Local Variables

By default, local variables require an @ prefix. However, you can specify plain locals, local variables that do not require an @ prefix. The following command activates plain local variables:

SET PLAINLOCALS ON

You must activate plain local variables before declaring these variables. With plain local variables activated you can declare both local variables with an @ prefix and local variables without an @ prefix. However, you cannot declare two variables that only differ by the @ prefix. For example, @myvar and myvar are considered the same variable. When declaring, selecting, or printing a plain local variable, you can specify the same variable with or without the @ prefix.

Plain local variables follow all of the other TSQL variable conventions.

The following TSQL class method specifies PLAINLOCALS ON and declares and uses both an @ local variable and a plain local variable:

ClassMethod Hello() As %String [Language=tsql,ReturnResultsets,SqlProc ]
{ SET PLAINLOCALS ON;
   DECLARE @a CHAR(20),b CHAR(20);
   SET @a='hello ' SET b='world!';
   PRINT @a,b;
}

@@ Special Variables

TSQL special variables are identified by an @@ prefix. @@ 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 InterSystems IRIS 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 ObjectScript and InterSystems 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.

ObjectScript 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.

  SET myquery = "SELECT @@FETCH_STATUS AS FetchStat"
  SET tStatement = ##class(%SQL.Statement).%New(,,"MSSQL")
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 { WRITE "%Prepare failed",$System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()

The corresponding InterSystems SQL function is:

SELECT $TSQL_FETCH_STATUS()

ObjectScript $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.

ObjectScript %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.

  SET myquery = "SELECT @@LOCK_TIMEOUT AS LockTime"
  SET tStatement = ##class(%SQL.Statement).%New(,,"MSSQL")
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 { WRITE "%Prepare failed",$System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()

The corresponding InterSystems SQL function is:

SELECT $TSQL_LOCK_TIMEOUT()

ObjectScript LOCK command

SQL SET OPTION LOCK_TIMEOUT

@@NESTLEVEL

Contains an integer specifying the nesting level of the current process.

  SET myquery = "PRINT @@NESTLEVEL"
  SET tStatement = ##class(%SQL.Statement).%New(,,"MSSQL")
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 { WRITE "%Prepare failed",$System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()

The corresponding InterSystems SQL function is:

SELECT $TSQL_NESTLEVEL()

ObjectScript $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.

ObjectScript %ROWCOUNT

SQL :%ROWCOUNT

@@SERVERNAME

Contains the InterSystems IRIS instance name.

  SET myquery = "SELECT @@SERVERNAME AS CacheInstance"
  SET tStatement = ##class(%SQL.Statement).%New(,,"MSSQL")
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 { WRITE "%Prepare failed",$System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()

The corresponding InterSystems SQL function is:

SELECT $TSQL_SERVERNAME()

ObjectScript $PIECE($system,":",2)

@@SPID

Contains the server process ID of the current process.

  SET myquery = "SELECT @@SPID AS ProcessID"
  SET tStatement = ##class(%SQL.Statement).%New(,,"MSSQL")
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 { WRITE "%Prepare failed",$System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()

The corresponding InterSystems SQL function is:

SELECT $TSQL_SPID()

ObjectScript $JOB

@@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.

  SET myquery = "SELECT @@SQLSTATUS AS SQLStatus"
  SET tStatement = ##class(%SQL.Statement).%New(,,"MSSQL")
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 { WRITE "%Prepare failed",$System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()

The corresponding InterSystems SQL function is:

SELECT $TSQL_SQLSTATUS()

ObjectScript $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.

  SET myquery = "SELECT @@TRANCOUNT AS ActiveTransactions"
  SET tStatement = ##class(%SQL.Statement).%New(,,"MSSQL")
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 { WRITE "%Prepare failed",$System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()

The corresponding InterSystems SQL function is:

SELECT $TSQL_TRANCOUNT()

ObjectScript $TLEVEL

@@VERSION

Contains the InterSystems IRIS version number and date and time of its installation.

  SET myquery = "SELECT @@VERSION AS CacheVersion"
  SET tStatement = ##class(%SQL.Statement).%New(,,"MSSQL")
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 { WRITE "%Prepare failed",$System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()

The corresponding InterSystems SQL function is:

SELECT $TSQL_VERSION()

ObjectScript $ZVERSION

FeedbackOpens in a new tab