Caché Transact-SQL (TSQL) Migration Guide
TSQL Commands
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

This chapter lists the supported TSQL commands in the following groups:

Data Definition Language (DDL) Statements
The following DDL statements are supported.
CREATE TABLE
The CREATE TABLE statement defines a table, its fields, and their data types and constraints.
CREATE TABLE [schema. | #]tablename (fieldname datattype constraint [,...])
A CREATE TABLE can create a temporary table by prefixing a # character to the table name. A temporary table can only be defined from a stored procedure; you cannot define a temporary table from Dynamic SQL outside of a stored procedure. To create a fully-qualified temporary table name, use quotes around each name element such as the following: "SQLUser"."#mytemp".
A valid table name must begin with a letter, an underscore character (_), or a # character (for a local temporary table). Subsequent characters of a table name may be letters, numbers, or the #, $, or _ characters. Table names are not case sensitive.
A field name must be a valid TSQL identifier. A field name can be delimited using square brackets. This is especially useful when defining a field that has the same name as a reserved word. The following example defines two fields named Check and Result:
CREATE TABLE mytest ([Check] VARCHAR(50),[Result] VARCHAR(5))
The optional CONSTRAINT keyword can be used to specify a user-defined constraint name for a column constraint or a table constraint. You can specify multiple CONSTRAINT name type statements for a column.
Caché SQL does not retain constraint names. Therefore these names cannot be used by a subsequent ALTER TABLE statement.
The table column constraints DEFAULT, IDENTITY, NULL, NOT NULL, PRIMARY KEY, [FOREIGN KEY] REFERENCES (the keywords FOREIGN KEY are optional), UNIQUE, CLUSTERED, and NONCLUSTERED are supported. The table constraint FOREIGN KEY REFERENCES is supported.
The column definition DEFAULT values can include the following TSQL functions: CURRENT_TIMESTAMP, CURRENT_USER, GETDATE, HOST_NAME, ISNULL, NULLIF, and USER.
The column definition IDENTITY constraint is supported and assigned a system-generated sequential integer. The IDENTITY arguments seed and increment are parsed, but ignored.
The table constraint clauses WITH, ON, and TEXTIMAGE ON are parsed for compatibility, but are ignored. The <index options> clause for the UNIQUE or PRIMARY KEY constraint is parsed for compatibility, but is ignored.
The following SQL Server parenthesized WITH options in a table constraint are parsed but ignored: ALLOW_PAGE_LOCKS, ALLOW_ROW_LOCKS, DATA_COMPRESSION, FILLFACTOR, IGNORE_DUP_KEY, PAD_INDEX, and STATISTICS_NORECOMPUTE.
The column constraints CLUSTERED and NONCLUSTERED are parsed for compatibility, but are ignored.
The CHECK column constraint is not supported. If a CHECK constraint is encountered while compiling TSQL source Caché generates an error message indicating that CHECK constraints are not supported. This error is logged in the compile log (if active), and the source is placed in the unsupported log (if active).
If the table already exists, an SQLCODE -201 error is issued.
The following Dynamic SQL example creates a temporary table named #mytest with four fields, populates it with data, then displays the results. The LastName field has multiple constraints. The FirstName field takes a default. The DateStamp field takes a system-defined default:
  SET sql=9
  SET sql(1)="CREATE TABLE #mytest (MyId INT PRIMARY KEY,"
  SET sql(2)="LastName VARCHAR(20) CONSTRAINT unq_lname UNIQUE "
  SET sql(3)="  CONSTRAINT nonull_lname NOT NULL,"
  SET sql(4)="FirstName VARCHAR(20) DEFAULT '***TBD***',"
  SET sql(5)="DateStamp DATETIME DEFAULT CURRENT_TIMESTAMP)"
  SET sql(6)="INSERT INTO #mytest(MyId,LastName,FirstName) VALUES (1224,'Smith','John')"
  SET sql(7)="INSERT INTO #mytest(MyId,LastName) VALUES (1225,'Jones')"
  SET sql(8)="SELECT MyId,FirstName,LastName,DateStamp FROM #mytest"
  SET sql(9)="DROP TABLE #mytest"
  SET statement=##class(%SQL.Statement).%New()
  SET statement.%Dialect="MSSQL"
  SET status=statement.%Prepare(.sql)
  WRITE status,!
  SET result=statement.%Execute()
  DO result.%Display()
 
ALTER TABLE
The ALTER TABLE statement allows you to modify the definition of a table, its fields, and their data types and constraints.
The following syntactical forms are supported:
ALTER TABLE <tablename> ADD <colname> <datatype> [DEFAULT <value>] [{UNIQUE | NOT NULL} | CONSTRAINT <constraintname> {UNIQUE | NOT NULL} ]
ALTER TABLE <tablename> ALTER COLUMN <colname> <newdatatype>
ALTER TABLE <tablename> REPLACE <colname> DEFAULT { <constant> | USER | NULL }
ALTER TABLE <tablename> DROP COLUMN <colname>
ALTER TABLE <tablename> ADD <tableconstraint> FOR <colname>
ALTER TABLE <tablename> DROP <tableconstraint>
ALTER TABLE <tablename> DROP FOREIGN KEY <role>
ALTER TABLE <tablename> ADD CONSTRAINT <constraint> DEFAULT <defaultvalue> FOR <colname> 
ALTER TABLE <tablename> ADD CONSTRAINT <constraint> FOREIGN KEY
ALTER TABLE <tablename> DROP CONSTRAINT <constraint>
ALTER TABLE <tablename> DISABLE
ALTER TABLE...ADD can only specify the NOT NULL constraint if it also specifies a DEFAULT value. The full supported syntax for ALTER TABLE...ADD is as follows:
ALTER TABLE <tablename> 
   [ WITH CHECK | WITH NOCHECK ]
  ADD <colname> <datatype> [DEFAULT <value>] [{UNIQUE | NOT NULL} | CONSTRAINT <constraintname> {UNIQUE | NOT NULL} ]
  [ FOREIGN KEY (col1[,col2[,...]])
     REFERENCES table(col1[,col2[,...]]) ]
WITH CHECK | WITH NOCHECK is parsed by Caché, but is ignored. In Transact-SQL, WITH CHECK | WITH NOCHECK provides an execution time check of existing data for a new or newly enabled constraint. Caché TSQL does not specifically support that, although Caché SQL will check existing data against a new constraint.
ALTER TABLE...ADD CONSTRAINT...DEFAULT syntax does not create a column constraint. Instead, it performs the equivalent of an ALTER TABLE...ALTER COLUMN...DEFAULT statement. This means that Caché establishes the specified column default as the field property’s initialexpression. Because no column constraint is defined, this “constraint” cannot be subsequently dropped or changed.
CHECK | NOCHECK CONSTRAINT is not supported by Caché TSQL. Specifying this CHECK or NOCHECK keyword generates an error message.
ALTER TABLE...DROP COLUMN: the keyword DELETE is a synonym for the keyword DROP.
DROP TABLE
Deletes a table definition.
DROP TABLE [IF EXISTS] tablename [,tablename2 [,...] ]
Deletes a table definition. You can delete a single table or a comma-separated list of tables. You can delete both regular tables and temporary tables. (Temporary table names begin with a '#' character.) DROP TABLE ignores a nonexistent temporary table name and completes without error.
The optional IF EXISTS clause suppresses errors if you specify a non-existent tablename that is not a temporary table.
CREATE INDEX
Creates an index for a specified table or view.
CREATE INDEX indexname ON tablename(fieldname)
You can create an index on the IDKEY (which is treated as a clustered index), on an IDENTITY column (which create an index on the %%ID column), on the Primary Key, or on other columns.
The following Transact-SQL features are parsed, but ignored:
The following Transact-SQL features are not currently supported:
DROP INDEX
Deletes an index definition. You can delete a single index or a comma-separated list of indices, using either of the following syntax forms:
DROP INDEX table.index [,table.index]

DROP INDEX index ON table [WITH (...)] [,index ON table [WITH (...)] ]
Where table is the name of the table containing the indexed field, and index is the name of the index.
The WITH (...) clause, with any value within the parentheses, is accepted by syntax checking for compatibility, but is not validated and performs no operation.
CREATE TRIGGER
Creates a statement-level trigger.
CREATE TRIGGER trigger_name {BEFORE | AFTER} 
{INSERT | DELETE | UPDATE [OF col1[,col2[,...]]]}
[ORDER integer]
ON table-name
[ REFERENCING OLD [ROW] [AS] name |
                                NEW [ROW] [AS] name |
                                OLD TABLE [AS] identifier |
                                NEW TABLE [AS] identifier ][,...]
[FOR EACH {ROW | STATEMENT}]
[WHEN (condition)]
[LANGUAGE {TSQL | SQL |OBJECTSCRIPT}]
triggered statement
[WITH EXECUTE]
Caché TSQL does not support row-level triggers.
Caché TSQL supports BEFORE triggers. This is a Caché-specific extension to TSQL which is not provided in Transact-SQL software from other vendors.
DROP TRIGGER
Deletes a trigger definition. You can delete a single trigger or a comma-separated list of triggers.
CREATE VIEW
CREATE VIEW [owner.]view_name [(colname1 [,colname2 [,...]])]
    [WITH ENCRYPTION | SCHEMABINDING | VIEW_METADATA]
    AS select_statement
   [WITH CHECK OPTION]
A view_name must be a unique TSQL identifier. If the view already exists, an SQLCODE -201 error is issued. A view_name can be a delimited identifier. For example, CREATE VIEW "Name/Age View"
There are two ways to specify the names of the view columns:
If neither is specified, the table column names are used as the view column names.
The WITH ENCRYPTION, SCHEMABINDING, and VIEW_METADATA keywords are ignored.
The select_statement can only include an ORDER BY clause if this clause is paired with a TOP clause. If you wish to include all of the rows in the view, you can pair an ORDER BY clause with a TOP ALL clause. You can include a TOP clause without an ORDER BY clause. However, if you include an ORDER BY clause without a TOP clause, an SQLCODE -143 error is generated.
The select_statement can contain a UNION or UNION ALL.
The optional WITH CHECK OPTION clause prevents an update through the view that makes the record inaccessible to that view. It does this by checking the WITH clause in the SELECT statement. WITH CHECK OPTION binds to Caché SQL using the default of CASCADE.
DROP VIEW
Deletes a view definition. You can delete a single view, or a comma-separated list of views. DROP VIEW is not an all-or-nothing operation. It deletes existing views from the list of views, and ignores nonexistent views in the list of views.
CREATE DATABASE
CREATE DATABASE syntax is parsed to provide compatibility with MSSQL. No functionality is provided.
The MSSQL attach a database and create a database snapshot syntax options are not supported.
DROP DATABASE
DROP DATABASE syntax is parsed to provide compatibility with MSSQL. No functionality is provided.
Data Management Language (DML) Statements
DELETE
Deletes rows of data from a table. Both DELETE and DELETE FROM are supported. Most other options are supported, with the following exceptions:
The following table_hints are parsed but ignored: FASTFIRSTROW, HOLDINDEX, INDEX(name), NOLOCK, PAGLOCK, READCOMMITTED, READPAST, READUNCOMMITTED, REPEATABLEREAD, ROWLOCK, SERIALIZABLE, SHARED, TABLOCK, TABLOCKX, UPDLOCK, XLOCK. Table hints can be optionally preceded by the WITH keyword, and, if WITH is specified, optionally enclosed in parentheses. A list of table hints can be separated by either commas or blank spaces.
DELETE sets the @@ROWCOUNT system variable to the number of rows deleted, and the @@IDENTITY system variable to the IDENTITY value of the last row deleted.
INSERT
Inserts rows of data into a table. The following MSSQL syntactic forms are supported:
INSERT column VALUES list_of_values
INSERT column SELECT select_statement
The corresponding Sybase list_of_values and SELECT syntactic forms are supported. (Sybase does not use the VALUES keyword.)
Most options are supported, with the following exceptions:
The following table_hints are parsed but ignored: FASTFIRSTROW, HOLDINDEX, INDEX(name), NOLOCK, PAGLOCK, READCOMMITTED, READPAST, READUNCOMMITTED, REPEATABLEREAD, ROWLOCK, SERIALIZABLE, SHARED, TABLOCK, TABLOCKX, UPDLOCK, XLOCK. Table hints can be optionally preceded by the WITH keyword, and, if WITH is specified, optionally enclosed in parentheses. A list of table hints can be separated by either commas or blank spaces.
INSERT sets the @@ROWCOUNT system variable to the number of rows inserted, and the @@IDENTITY system variable to the IDENTITY value of the last row inserted.
UPDATE
Updates values of existing rows of data in a table.
UPDATE tablename SET fieldname=value[,fieldname2=value2[,...]]
   WHERE [tablename.]fieldname=value
Most UPDATE options are supported, with the following exceptions:
UPDATE supports the use of a local variable on the left-hand-side of a SET clause. This local variable can be either instead of a column name or in addition to a column name. The following example shows a SET to a column name, a SET to a local variable, and a SET to both a column name and a local variable:
UPDATE table SET x=3,@v=b,@c=Count=Count+1
If a local variable is specified on the left-hand-side of a SET, the right-hand-side cannot be DEFAULT.
The following table_hints are parsed but ignored: FASTFIRSTROW, HOLDINDEX, INDEX(name), NOLOCK, PAGLOCK, READCOMMITTED, READPAST, READUNCOMMITTED, REPEATABLEREAD, ROWLOCK, SERIALIZABLE, SHARED, TABLOCK, TABLOCKX, UPDLOCK, XLOCK. Table hints can be optionally preceded by the WITH keyword, and, if WITH is specified, optionally enclosed in parentheses. A list of table hints can be separated by either commas or blank spaces.
UPDATE sets the @@ROWCOUNT system variable to the number of rows updated, and the @@IDENTITY system variable to the IDENTITY value of the last row updated.
The following Dynamic SQL example shows a simple UPDATE operation:
  SET sql=9
  SET sql(1)="CREATE TABLE #mytest (MyId INT PRIMARY KEY,"
  SET sql(2)="LastName VARCHAR(20) CONSTRAINT nonull_lname NOT NULL,"
  SET sql(3)="FirstName VARCHAR(20) DEFAULT '***TBD***')"
  SET sql(4)="INSERT INTO #mytest(MyId,LastName,FirstName) VALUES (1224,'Smith','John')"
  SET sql(5)="INSERT INTO #mytest(MyId,LastName) VALUES (1225,'Jones')"
  SET sql(6)="INSERT INTO #mytest(MyId,LastName) VALUES (1226,'Brown')"
  SET sql(7)="UPDATE #mytest SET FirstName='Fred' WHERE #mytest.LastName='Jones'"
  SET sql(8)="SELECT FirstName,LastName FROM #mytest ORDER BY LastName"
  SET sql(9)="DROP TABLE #mytest"
  SET statement=##class(%SQL.Statement).%New()
  SET statement.%Dialect="MSSQL"
  SET status=statement.%Prepare(.sql)
  WRITE status,!
  SET result=statement.%Execute()
  DO result.%Display()
 
READTEXT, UDATETEXT, WRITETEXT
READTEXT returns stream data from a column of a table. It requires a valid text pointer value, which can be retrieved using the TEXTPTR function.
UPDATETEXT updates stream data from a column of a table. It requires a valid text pointer value, which can be retrieved using the TEXTPTR function.
WRITETEXT writes data to a stream column of a table. It requires a valid text pointer value, which can be retrieved using the TEXTPTR function.
TRUNCATE TABLE
Deletes all rows from the specified table. Supported to the extent that it is considered a synonym for DELETE FROM table with no WHERE clause. However, TRUNCATE TABLE does not reset the IDENTITY field counter.
Query Statements
SELECT
SELECT  [DISTINCT | ALL] 
  [TOP [(]{ int | @var | ? | ALL}[)]]
  select-item {,select-item}
  [INTO #temptable]
  [FROM table [[AS] t-alias] [,table2 [[AS] t-alias2]] ]
  [[WITH] [(] tablehint=val [,tablehint=val] [)] ]
  [WHERE condition-expression]
  [GROUP BY scalar-expression]
  [HAVING condition-expression]
  [ORDER BY item-order-list [ASC | DESC] ]
The above SELECT syntax is supported. The following features are not supported:
TOP nn specifies the number of rows to retrieve. Caché TSQL supports TOP nn with a integer, ?, local variable, or the keyword ALL. The TOP argument can be enclosed in parentheses TOP (nn). These parentheses are retained, preventing preparser substitution. If SET ROWCOUNT specifies fewer rows than TOP nn, the SET ROWCOUNT value is used. The following Dynamic SQL example shows the use of TOP with a local variable:
  SET sql=3
  SET sql(1)="DECLARE @var INT"
  SET sql(2)="SET @var=4"
  SET sql(3)="SELECT TOP @var Name,Age FROM Sample.Person"
  SET statement=##class(%SQL.Statement).%New()
  SET statement.%Dialect="MSSQL"
  SET status=statement.%Prepare(.sql)
  SET result=statement.%Execute()
  DO result.%Display()
 
Table hints are supported with either of the following syntactic forms:
FROM tablename (INDEX=indexname)
FROM tablename INDEX (indexname)
Table hints can be optionally preceded by the WITH keyword, and optionally enclosed in parentheses. A list of table hints can be separated by either commas or blank spaces. The following table hints are parsed but ignored: FASTFIRSTROW, HOLDINDEX, NOLOCK, PAGLOCK, READCOMMITTED, READPAST, READUNCOMMITTED, REPEATABLEREAD, ROWLOCK, SERIALIZABLE, SHARED, TABLOCK, TABLOCKX, UPDLOCK, XLOCK.
SELECT * is supported. The asterisk means to select all columns in the specified table. You can qualify the asterisk with the table name or table alias: SELECT mytable.*.
A SELECT query can contain a subquery in its select list.
A Select List can contain stream fields. A SELECT on a stream field returns the oref (object reference) of the opened stream object.
A TSQL SELECT does not require a FROM clause. A SELECT without a FROM clause can be used to assign a value to a local variable, as follows:
DECLARE @myvar INT
SELECT @myvar=1234
PRINT @myvar
An ORDER BY clause can specify ascending (ASC) or descending (DESC) order. The default is ascending. Unlike Caché SQL, an ORDER BY may be used in subqueries and in queries that appear in expressions. For example:
SET @var = (SELECT TOP 1 name FROM mytable ORDER BY name)
An INTO clause can be used to select values into a table. This table can be a permanent table, or a temporary table, as shown in the following example:
SELECT name,age INTO #MyTemp FROM Sample.Person
SELECT name,age FROM #MyTemp WHERE name LIKE '[A-D]%'
If the INTO table does not already exist, SELECT creates it.
An INTO clause cannot be used when the SELECT is a subquery or is part of a UNION.
A WHERE clause can use AND, OR, and NOT logic keywords. It can group multiple search conditions using parentheses. The WHERE clause supports the following search conditions:
WHERE clause and HAVING clause comparisons are not case sensitive.
A HAVING clause can be specified after a GROUP BY clause. The HAVING clause is like a WHERE clause that can operate on groups, rather than on the full data set. HAVING and WHERE use the same comparisons. This is shown in the following example:
SELECT Home_State, MIN(Age) AS Youngest,
  AVG(Age) AS AvgAge, MAX(Age) AS Oldest
 FROM Sample.Person
 GROUP BY Home_State
 HAVING Age < 21
 ORDER BY Youngest
The following Dynamic SQL example selects table data into a result set:
  SET sql=7
  SET sql(1)="CREATE TABLE #mytest (MyId INT PRIMARY KEY,"
  SET sql(2)="LastName VARCHAR(20),"
  SET sql(3)="FirstName VARCHAR(20))"
  SET sql(4)="INSERT INTO #mytest(MyId,LastName,FirstName) VALUES (1224,'Smith','John')"
  SET sql(5)="INSERT INTO #mytest(MyId,LastName,FirstName) VALUES (1225,'Jones','Wilber')"
  SET sql(6)="SELECT FirstName,LastName FROM #mytest"
  SET sql(7)="DROP TABLE #mytest"
  SET statement=##class(%SQL.Statement).%New()
  SET statement.%Dialect="MSSQL"
  SET status=statement.%Prepare(.sql)
  SET result=statement.%Execute()
  DO result.%Display()
 
The following Dynamic SQL example selects a single column value into a local variable:
  SET sql=9
  SET sql(1)="CREATE TABLE #mytest (MyId INT PRIMARY KEY,"
  SET sql(2)="LastName VARCHAR(20),"
  SET sql(3)="FirstName VARCHAR(20))"
  SET sql(4)="INSERT INTO #mytest(MyId,LastName,FirstName) VALUES (1224,'Smith','John')"
  SET sql(5)="INSERT INTO #mytest(MyId,LastName,FirstName) VALUES (1225,'Jones','Wilber')"
  SET sql(6)="DECLARE @nam VARCHAR(20)"
  SET sql(7)="SELECT @nam=LastName FROM #mytest"
  SET sql(8)="PRINT @nam"
  SET sql(9)="DROP TABLE #mytest"
  SET statement=##class(%SQL.Statement).%New()
  SET statement.%Dialect="MSSQL"
  SET status=statement.%Prepare(.sql)
  DO statement.%Execute()
 
JOIN
JOIN (equivalent to INNER JOIN), INNER JOIN, and LEFT JOIN supported. Parentheses can be used to rationalize parsing of multiple joins.
Note:
Caché TSQL uses the following symbolic representations for outer joins:
=*   Left Outer Join
*=   Right Outer Join
These correspond to Caché SQL usage. They are the exact opposite of the SQL Server and Sybase join syntax (where =* is a Right Outer Join). It is strongly recommended that you represent outer joins using ANSI standard keyword syntax, rather than this symbolic syntax.
UNION
A union of two (or more) SELECT statements is supported. Caché TSQL supports UNION and UNION ALL. If you specify UNION ALL, only the first SELECT can specify an INTO table. This INTO table can be a defined table, or a temporary table generated from the SELECT column list.
FETCH Cursor
The OPEN, FETCH, CLOSE, and DEALLOCATE commands are mainly supported. The following features are not supported:
UPDATE STATISTICS
Optimizes query access for a specified table. Caché passes the specified table name argument to the $SYSTEM.SQL.TuneTable() method for optimization. UPDATE STATISTICS calls $SYSTEM.SQL.TuneTable() with update=1 and display=0. The returned %msg is ignored and KeepClassUpToDate defaults to 'false'. All other UPDATE STATISTICS syntax is parsed for compatibility only and ignored. In a batch or stored procedure, only the first UPDATE STATISTICS statement for a given table generates a call to $SYSTEM.SQL.TuneTable().
Flow of Control Statements
IF
The IF command is supported with four syntactic forms:
IF...ELSE syntax:
IF condition
statement
[ELSE statement]
IF...THEN...ELSE single-line syntax:
IF condition THEN statement [ELSE statement]
ELSEIF...END IF syntax:
IF condition THEN 
statements
{ELSEIF condition THEN statements}
[ELSE statements]
END IF
ELSE IF (SQL Anywhere) syntax:
IF condition THEN statement
{ELSE IF condition THEN statement}
[ELSE statement]
The first syntactic form is the TSQL standard format. No THEN keyword is used. You may use white space and line breaks freely. To specify more than one statement in a clause you must use BEGIN and END keywords to demarcate the block of statements. The ELSE clause is optional. This syntax is shown in the following example:
  SET sql=4
  SET sql(1)="DECLARE @var INT"
  SET sql(2)="SET @var=RAND()"
  SET sql(3)="IF @var<.5 PRINT 'The Oracle says No'"
  SET sql(4)="ELSE PRINT 'The Oracle says Yes' "
  SET statement=##class(%SQL.Statement).%New()
  SET statement.%Dialect="MSSQL"
  SET status=statement.%Prepare(.sql)
  SET result=statement.%Execute()
  DO result.%Display()
 
The second syntactic form is single-line syntax. The THEN keyword is required. A line break restriction requires that IF condition THEN statement all be on the same line, though only the first keyword of the statement must be on that line. Otherwise, you may use white space and line breaks freely. To specify more than one statement in a clause you must use BEGIN and END keywords to demarcate the block of statements. The ELSE clause is optional. This syntax is shown in the following example:
  SET sql=3
  SET sql(1)="DECLARE @var INT "
  SET sql(2)="SET @var=RAND() "
  SET sql(3)="IF @var<.5 THEN PRINT 'No' ELSE PRINT 'Yes' "
  SET statement=##class(%SQL.Statement).%New()
  SET statement.%Dialect="MSSQL"
  SET status=statement.%Prepare(.sql)
  SET result=statement.%Execute()
  DO result.%Display()
 
The third syntactic form provides an ELSEIF clause. You can specify zero, one, or more than one ELSEIF clauses, each with its own condition test. Within an IF, ELSEIF, or ELSE clause you can specify multiple statements. BEGIN and END keywords are permitted but not required. A line break restriction requires a line break between IF condition THEN and the first statement. Otherwise, you may use white space and line breaks freely. The ELSE clause is optional. The END IF keyword clause is required. This syntax is shown in the following example:
  SET sql=14
  SET sql(1)="DECLARE @var INT "
  SET sql(2)="SET @var=RAND() "
  SET sql(3)="IF @var<.2 THEN "
  SET sql(4)="PRINT 'The Oracle' "
  SET sql(5)="PRINT 'says No' "
  SET sql(6)="ELSEIF @var<.4 THEN "
  SET sql(7)="PRINT 'The Oracle' "
  SET sql(8)="PRINT 'says Possibly' "
  SET sql(9)="ELSEIF @var<.6 THEN "
  SET sql(10)="PRINT 'The Oracle' "
  SET sql(11)="PRINT 'says Probably' "
  SET sql(12)="ELSE PRINT 'The Oracle' "
  SET sql(13)="PRINT 'says Yes' "
  SET sql(14)="END IF"
  SET statement=##class(%SQL.Statement).%New()
  SET statement.%Dialect="MSSQL"
  SET status=statement.%Prepare(.sql)
  SET result=statement.%Execute()
  DO result.%Display()
 
The fourth syntactic form is compatible with SQL Anywhere. It provides an ELSE IF clause (note space between keywords). You can specify zero, one, or more than one ELSE IF clauses, each with its own condition test. To specify more than one statement in a clause you must use BEGIN and END keywords to demarcate the block of statements. You may use white space and line breaks freely. The ELSE clause is optional. This syntax is shown in the following example:
  SET sql=6
  SET sql(1)="DECLARE @var INT "
  SET sql(2)="SET @var=RAND() "
  SET sql(3)="IF @var<.2 THEN PRINT 'The Oracle says No'"
  SET sql(4)="ELSE IF @var<.4 THEN PRINT 'The Oracle says Possibly'"
  SET sql(5)="ELSE IF @var<.6 THEN PRINT 'The Oracle says Probably'"
  SET sql(6)="ELSE PRINT 'The Oracle says Yes'"
  SET statement=##class(%SQL.Statement).%New()
  SET statement.%Dialect="MSSQL"
  SET status=statement.%Prepare(.sql)
  SET result=statement.%Execute()
  DO result.%Display()
 
WHILE
The WHILE command is supported. It executes code while a condition is true.
WHILE condition BEGIN statements END
The BEGIN and END keywords are required if statements is more than one command.
CASE
The CASE statement is supported. The ELSE clause is optional. If no WHEN clause is satisfied and no ELSE clause is specified, the CASE statement sets the expression to NULL.
GOTO and Labels
Caché TSQL supports the GOTO command and labels. A label must be a valid TSQL identifier followed by a colon (:). A GOTO reference to a label does not include the colon.
WAITFOR
Caché TSQL supports both forms of the WAITFOR command:
WAITFOR DELAY timeperiod
WAITFOR TIME clocktime
timeperiod is the amount of time to wait before resuming execution, expressed as 'hh:mm[:ss[.fff]]. Thus WAITFOR DELAY '00:00:03' provides a time delay of 3 seconds. WAITFOR DELAY '00:03' provides a time delay of 3 minutes.
clocktime is the time at which to resume execution, expressed as 'hh:mm[:ss[.fff]], using a 24-hour clock. Thus WAITFOR TIME '00:00:03' resumes execution at 3 seconds after midnight.
Assignment Statements
DECLARE
Used to declare the data type for a local variable.
DECLARE @var [AS] datatype [ = initval]
Only the form which declares local variables is supported; cursor variables are not supported.
@var can be any local variable name; local variable names are not case-sensitive. datatype can be any valid data type, such as CHAR(12) or INT. The optional initval argument allows you to specify an initial value for the local variable.
For further details on data types, refer to the TSQL Constructs chapter of this document.
SET
Used to assign a value to a local variable:
DECLARE @var CHAR(20)
SET @var='hello world'
Used to set a system setting:
These settings have immediate effect at parse time, whether inside a stored procedure or not. The change persists until another SET command alters it – even if the SET is made inside a stored procedure, and accessed outside the SP or in another SP.
The following SET variables are supported:
The following SET variables are parsed, but ignored:
Transaction Statements
Caché TSQL provides support for transactions, including named transaction names. It does not support savepoints. Distributed transactions are not supported.
SET TRANSACTION ISOLATION LEVEL
Supported for the following forms only:
BEGIN TRANSACTION
BEGIN TRAN [name]
BEGIN TRANSACTION [name]
Initiates a transaction. The optional name argument can be used to specify a named transaction, also known as a savepoint. The name value must be supplied as a literal; it cannot be a variable.
COMMIT TRANSACTION
COMMIT
COMMIT TRAN
COMMIT TRANSACTION
COMMIT WORK
These four syntactical forms are functionally identical. A COMMIT statement commits all work completed during the current transaction, resets the transaction level counter, and releases all locks established. This completes the transaction. Work committed cannot be rolled back.
A transaction is defined as the operations since and including the BEGIN TRANSACTION statement. A COMMIT restores the transaction level counter to its state immediately prior to the BEGIN TRANSACTION statement that initialized the transaction.
A single COMMIT causes all named transactions to be committed.
ROLLBACK TRANSACTION
ROLLBACK [name]
ROLLBACK TRAN [name]
ROLLBACK TRANSACTION [name]
ROLLBACK WORK [name]
These four syntactical forms are functionally identical; the ROLLBACK keyword, as specified below, refers to any of these syntactical forms. The optional name argument specifies a named transaction, as specified by a BEGIN TRANSACTION name statement. The name value must be supplied as a literal; it cannot be a variable.
A ROLLBACK rolls back a transaction, undoing work performed but not committed, decrementing the transaction level counter, and releasing locks. It is used to restore the database to a previous consistent state.
CHECKPOINT
The CHECKPOINT statement is parsed but ignored in Caché TSQL. It performs no operation.
LOCK TABLE
LOCK TABLE tablename IN {SHARE | EXCLUSIVE} MODE [WAIT numsecs | NOWAIT]
The LOCK TABLE statement locks all of the records in the specified table. You can lock a table in SHARE MODE or in EXCLUSIVE MODE. The optional WAIT clause specifies the number of seconds to wait in attempting to acquire the table lock. The LOCK TABLE statement immediately releases any prior lock held by the current user on the specified table. LOCK TABLE locks the table for the duration of the current transaction.
Procedure Statements
The following standard Transact-SQL statements are supported.
CREATE PROCEDURE / CREATE FUNCTION
Creates a named executable procedure.
CREATE PROCEDURE procname [[@var [AS] datatype [= | DEFAULT value] [,...]] [AS] code
CREATE PROC procname [[@var [AS] datatype [= | DEFAULT value] [,...]] [AS] code
CREATE FUNCTION procname [[@var [AS] datatype [= | DEFAULT value] [,...]] [AS] code
You can return a single scalar value result from either a PROCEDURE or a FUNCTION. OUTPUT parameters and default values are also supported. These commands convert the return type from a TSQL type declaration to a Caché type descriptor. Currently, result sets and tables can't be returned.
Supported as either CREATE PROCEDURE or CREATE PROC. CREATE FUNCTION is very similar to CREATE PROCEDURE, but the routine type argument value is "FUNCTION", rather than "PROCEDURE".
A CREATE PROCEDURE can specify a formal parameter list. Formal parameters are specified as a comma-separated list. Enclosing parentheses are optional. The AS keyword between the parameter variable and its data type is optional. Optionally, you can use the DEFAULT keyword or = symbol to assign a default value to a formal parameter; if no actual parameter value is specified, this default value is used. In TSQL an input formal parameter has no keyword indicator; an output formal parameter can be specified by the OUTPUT keyword following the data type. Alternatively, these formal parameters can be prefaced by the optional keywords IN, OUT, or INOUT.
The following example shows the creation of the procedure AvgAge with two formal parameters:
CREATE PROCEDURE AvgAge @min INT, @max INT
AS
BEGIN TRY
  SELECT AVG(Age) FROM Sample.Person
  WHERE Age > @min AND Age < @max
END TRY
BEGIN CATCH
  PRINT 'error!'
END CATCH
The following statement executes this procedure. In this case, the specified actual parameter values limit the averaging to ages 21 through 65:
EXEC AvgAge 20,66
The following example shows the creation of procedure OurReply:
CREATE PROCEDURE OurReply @var CHAR(16) DEFAULT 'No thanks' AS PRINT @var
When executed without a parameter, OurReply prints the default text (“No thanks”); when executed with a parameter OurReply prints the actual parameter value specified in the EXEC statement.
Note that CREATE FUNCTION and CREATE PROCEDURE cannot be issued from a stored procedure.
Importing a CREATE PROCEDURE
If imported TSQL source contains a CREATE PROC statement, then a class method containing the CREATE PROC source will be created. This class method is either placed in an existing class, or in a new class whose name is based on the schema and procedure name.
If the procedure already exists, the existing implementation is replaced. If a class matching the class name generated from the schema and procedure already exists, it is used if it was previously generated by the TSQL utility. If not, then a unique class name is generated, based on the schema and procedure name. The schema defaults to the default schema defined in the system configuration. The resulting class is compiled once the procedure has been successfully created.
If logging is requested, the source statements are logged along with the name of the containing class, class method, and the formal arguments generated. Any errors encountered by the process are also reported in the log. If errors are detected during CREATE PROC processing and a new class was generated, that class is deleted.
ALTER FUNCTION
Supported. The WITH EXECUTE keyword clause is supported.
DROP FUNCTION
Supported.
DROP PROCEDURE
Deletes a procedure or a comma-separated list of procedures.
DROP PROCEDURE [IF EXISTS] procname [,procname2 [,...] ]
DROP PROC [IF EXISTS] procname [,procname2 [,...] ]
The optional IF EXISTS clause suppresses errors if you specify a non-existent procname. If this clause is not specified, an SQLCODE -362 error is generated if you specify a non-existent procname. DROP PROCEDURE is an atomic operation; either all specified procedures are successfully deleted or none are deleted.
RETURN
Halts execution of a query or procedure. Can be argumentless or with an argument. Argumentless RETURN must be used when exiting a TRY or CATCH block. When returning from a procedure, RETURN can optionally return an integer status code. If you specify no status code, it returns the empty string ("").
EXECUTE
Executes a procedure, optionally passing in parameters.
EXECUTE procname [param1 [,param2 [,...] ] ]
EXEC procname [param1 [,param2 [,...] ] ]
The EXECUTE (or EXEC) statement is supported as follows:
CALL
The CALL statement is functionally identical to the EXECUTE statement. It differs syntactically.
CALL procname ([param1 [,param2 [,...] ] ])
@var = CALL procname ([param1 [,param2 [,...] ] ])
The parameters are optional. The enclosing parentheses are mandatory.
Other Statements
CREATE USER
CREATE USER creates a new user ID and password. Executing this statement creates a Caché User with its password set to the specified user name.
User names are not case-sensitive. Caché TSQL and Caché SQL both use the same set of defined user names.
CREATE ROLE
CREATE ROLE creates a new role. A role is a named set of privileges that may be assigned to multiple users. A role may be assigned to multiple users, and a user may be assigned multiple roles. The following syntax is supported; the AUTHORIZATION clause is optional:
CREATE ROLE rolename AUTHORIZATION auth
GRANT and REVOKE
GRANT privilegelist ON (columnlist) TO granteelist
You can use the REVOKE command to explicitly remove granted privileges.
REVOKE privilegelist ON (columnlist) FROM granteelist CASCADE
PRINT
Supported.
RAISERROR
RAISERROR err_num 'message'
RAISERROR(error,severity,state,arg) WITH LOG
Both syntactic forms (with and without parentheses) are supported. Both spellings, RAISERROR and RAISEERROR, are supported and synonymous. RAISERROR sets the value of @@ERROR to the specified error number and error message and invokes the %SYSTEM.Error.FromXSQL() method.
The Sybase-compatible syntax (without parentheses) requires an err_num error number, the other arguments are optional.
RAISERROR 123 'this is a big error'
PRINT @@ERROR
A RAISERROR command raises an error condition; it is left to the user code to detect this error. However, if RAISERROR appears in the body of a TRY block, it transfers control to the paired CATCH block. If RAISERROR appears in a CATCH block it transfers control either to an outer CATCH block (if it exists) or to the procedure exit. RAISERROR does not trigger an exception outside of the procedure. It is up to the caller to check for the error.
When an AFTER statement level trigger executes a RAISEERROR, the returned %msg value contains the err_num and message values as message string components separated by a comma: %msg="err_num,message".
The Microsoft-compatible syntax (with parentheses) requires an error (either an error number or a quoted error message). If you do not specify an error number, it defaults to 50000. The optional severity and state arguments take integer values.
RAISERROR('this is a big error',4,1) WITH LOG
PRINT @@ERROR
UPDATE STATISTICS
Supported. Provides query performance optimization. UPDATE STATISTICS generates a call to the Caché $SYSTEM.SQL.TuneTable() method, passing the table name. For further details, see Tune Table in Caché SQL Optimization Guide.
USE database
Supported, also an extension: USE NONE to select no database. Effective at generation-time, persists as long as the transform object exists (e.g. in the shell or loading a batch).
InterSystems Extensions
TSQL supports a number of InterSystems extensions to Transact-SQL. To allow for the inclusion of these InterSystems-only statements in portable code, Caché TSQL also supports a special form of the single-line comment: two hyphens followed by a vertical bar. This operator is parsed as a comment by Transact-SQL implementations, but is parsed as an executable statement in Caché TSQL. For further details, refer to the Comments section of the TSQL Constructs chapter of this document.
TSQL includes the following InterSystems extensions:
CACHE
This extension allows you to include Caché ObjectScript code in the compiled output. It takes one or more lines of Caché ObjectScript inside curly brackets:
CACHE {WRITE "SQLCODE=",SQLCODE,!}
Note that in the above example the WRITE command specifies a new line (,!); this is necessary because the CACHE extension does not issue a new line following execution.
The CACHE extension can also be used to embed Caché SQL into TSQL code:
CACHE {&sql(SET TRANSACTION %COMMITMODE EXPLICIT)}
For further details on embedded Caché SQL, refer to Embedded SQL in Using Caché SQL.
IMPORTASQUERY
This extension forces a stored procedure to be imported as a query rather than as a class method. This is useful for stored procedures that contain only an EXEC statement, because Caché cannot otherwise determine at import whether such a stored procedure is a query or not.