TSQL Commands

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 <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[,...]]) ]

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 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 CLUSTERED/NONCLUSTERED keywords. Other than the IDKEY, which is implicitly treated as a clustered index, Caché TSQL does not support clustered indexes.

• The ASC/DESC keywords.

• The INCLUDE clause.

• All WITH clause index options. The comma-separated list of WITH clause options can optionally be enclosed in parentheses, and can include flag options set =ON or =OFF.

• The ON filegroup or IN dbspace-name clause.

The following Transact-SQL features are not currently supported:

• The DATA clause.

• The VIRTUAL keyword.

• Using a function name as an alternative to a column name.

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

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:

• You can use the optional colname comma-separated list to specify view column names for the corresponding table columns returned by the SELECT statement.

  CREATE VIEW NameAgeV (FullName,Years)
  AS SELECT Name,Age FROM Sample.Person

  

• You can specify column aliases in the SELECT statement, and these aliases are used as the view column names.

  CREATE VIEW NameAgeV
  AS SELECT Name AS FullName,Age AS Years FROM Sample.Person

  

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.

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:

• EXECUTE as a value clause

• rowset functions

• DEFAULT VALUES

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 tablename SET fieldname=value[,fieldname2=value2[,...]]
   WHERE [tablename.]fieldname=value

Most UPDATE options are supported, with the following exceptions:

• rowset

• OPTION

• only very simple theta joins are supported (the FROM table clause is transformed into nested subqueries)

• table hints

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()

TRUNCATE TABLE tablename

Deletes all rows from the specified table. Supported to the extent that it is a synonym for DELETE FROM table with no WHERE clause. However, TRUNCATE TABLE does not reset the RowId (ID), IDENTITY, or SERIAL (%Counter) row counters. The InterSystems SQL TRUNCATE TABLE command does reset these counters.

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 PERCENT or TOP WITH TIES

• OPTION

• WITH CUBE

• WITH ROLLUP

• GROUP BY ALL

• GROUP WITH

• COMPUTE clause

• FOR BROWSE

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()

The select-item list can contain the following:

• field names, functions, and expressions

• the $IDENTITY pseudo-column name, which always returns the RowID value, regardless of the field name assigned to the RowID.

• an asterisk: 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 subquery

• stream fields. A SELECT on a stream field returns the oref (object reference) of the opened stream object.

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.

The FROM clause is not required. 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

The FROM clause supports table hints 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.

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:

• Equality comparisons: = (equals), <> (not equals), < (less than). > (greater than), <= (less than or equals), >= (greater than or equals)

• IS NULL and IS NOT NULL comparisons

• BETWEEN comparisons: Age BETWEEN 21 AND 65 (inclusive of 21 and 65); Age NOT BETWEEN 21 AND 65 (exclusive of 21 and 65). BETWEEN is commonly used for a range of numeric values, which collate in numeric order. However, BETWEEN can be used for a collation sequence range of values of any data type. It uses the same collation type as the column it is matching against. By default, string data types collate as not case-sensitive.

• IN comparisons: Home_State IN ('MA','RI','CT')

• LIKE and NOT LIKE comparisons, specified as a quoted string. The comparison string can contain wildcards: _ (any single character); % (any string); [abc] (any value in the set specified as a list of items); [a-c] (any value in the set specified as a range of items). Caché TSQL does not support the ^ wildcard. A LIKE comparison can include an ESCAPE clause, such as the following: WHERE CategoryName NOT LIKE 'D\_%' ESCAPE '\'.

• EXISTS comparison check: used with a subquery to test whether the subquery evaluates to the empty set. For example SELECT Name FROM Sample.Person WHERE EXISTS (SELECT LastName FROM Sample.Employee WHERE LastName='Smith'). In this example, all Names are returned from Sample.Person if a record with LastName='Smith' exists in Sample.Employee. Otherwise, no records are returned from Sample.Person.

• ANY and ALL comparison check: used with a subquery and an equality comparison operator. The SOME keyword is a synonym for ANY.

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()

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)

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 condition BEGIN statements END

The BEGIN and END keywords are required if statements is more than one 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.

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.

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

• A ROLLBACK rolls back all work completed during the current transaction, resets the transaction level counter to zero and releases all locks. This restores the database to its state before the beginning of the transaction.

• A ROLLBACK name rolls back all work done since the specified named transaction (savepoint) and decrements the transaction level counter by the number of savepoints undone. When all savepoints have been either rolled back or committed and the transaction level counter reset to zero, the transaction is completed. If the named transaction does not exist, or has already been rolled back, ROLLBACK rolls back the entire current transaction.

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.

CREATE PROCEDURE procname [[@var [AS] datatype [= | DEFAULT value] [,...]] [RETURNS datatype] [AS] code
CREATE PROC procname [[@var [AS] datatype [= | DEFAULT value] [,...]] [RETURNS datatype] [AS] code
CREATE FUNCTION procname [[@var [AS] datatype [= | DEFAULT value] [,...]] [RETURNS datatype] [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".

• Any statements can be used in a CREATE FUNCTION.

• The RETURN keyword is allowed in a CREATE PROCEDURE. If a procedure completes without invoking a RETURN or RAISERROR statement, it returns an integer value of 0.

• The WITH EXECUTE keyword clause is allowed in a CREATE PROCEDURE and CREATE FUNCTION. It must appear after the RETURN keyword.

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 creates a procedure that returns the results of a division operation. The RETURNS keyword limits the number of decimal digits in the return value:

CREATE PROCEDURE SQLUser.MyDivide @a INTEGER, @b INTEGER, OUT @rtn INTEGER RETURNS DECIMAL(2,3)
BEGIN
SET @rtn = @a / @b;
RETURN @rtn;
END

The following statement executes this procedure:

SELECT SQLUser.MyDivide(7,3)

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.

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.

EXECUTE procname [param1 [,param2 [,...] ] ]
EXEC procname [param1 [,param2 [,...] ] ]

The EXECUTE (or EXEC) statement is supported as follows:

• EXEC <procedure> can be used to execute a stored procedure. The EXEC keyword cannot be omitted. Named parameters are supported. The following EXEC <procedure> features are not currently supported: procedure variables, and procedure numbers (i.e. ';n'). WITH RECOMPILE is accepted but ignored.

  CREATE PROCEDURE ProcTopSample
  AS SELECT TOP 4 Name,Age FROM Sample.Person
  GO

  

  EXEC ProcTopSample
  GO

  

  The following example executes the Caché-supplied procedure Sample.PersonSets, passing it two parameters:

  EXECUTE Sample.PersonSets 'F','VT'

  

  If the specified procedure does not exist, an SQLCODE -428 error (Stored procedure not found) is issued.

• EXEC (<TSQL commands>) can be used to execute dynamic SQL. The string of TSQL commands to be executed are enclosed in single quote characters. A TSQL command string can contain line breaks and white space. Dynamic TSQL runs in the current context.

  DECLARE @DynTopSample VARCHAR(200)
  SELECT @DynTopSample='SELECT TOP 4 Name,Age FROM Sample.Person'
  EXEC(@DynTopSample)

  

CALL procname ([param1 [,param2 [,...] ] ])
@var = CALL procname ([param1 [,param2 [,...] ] ])

The parameters are optional. The enclosing parentheses are mandatory.

GRANT privilegelist ON (columnlist) TO granteelist

• privelegelist: a single privilege or a comma-separated list of privileges. The available privileges are ALL, EXECUTE, SELECT, INSERT, DELETE, UPDATE, and REFERENCES.

• columnlist: a single column name or a comma-separated list of column names, enclosed in parentheses. You can specify a parenthesized list of column names at the end of the GRANT statement. For SELECT and UPDATE privileges, you can specify a parenthesized list of column names following that privilege keyword.

• granteelist: a single grantee (recipient of privileges) or a comma-separated list of grantees. A grantee can be a user name, "PUBLIC" or "*".

You can use the REVOKE command to explicitly remove granted privileges.

REVOKE privilegelist ON (columnlist) FROM granteelist CASCADE

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()Opens in a new tab 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