Transact-SQL (TSQL) Migration Guide
TSQL Commands
|
|
This chapter lists the supported TSQL commands for InterSystems IRIS Data Platform™ in the following groups:
The following DDL statements are supported.
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.
InterSystems 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 InterSystems IRIS 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()
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> DISABLEALTER 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 InterSystems IRIS, 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. InterSystems TSQL does not specifically support that, although InterSystems 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 InterSystems IRIS 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 InterSystems IRIS TSQL. Specifying this CHECK or NOCHECK keyword generates an error message.
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.
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 CLUSTERED/NONCLUSTERED keywords. Other than the IDKEY, which is implicitly treated as a clustered index, InterSystems TSQL does not support clustered indexes.
-
-
-
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:
-
-
-
Using a function name as an alternative to a column name.
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.
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]
InterSystems TSQL does not support row-level triggers.
InterSystems TSQL supports BEFORE triggers. This is an InterSystems IRIS-specific extension to TSQL which is not provided in Transact-SQL software from other vendors.
Deletes a trigger definition. You can delete a single trigger or a comma-separated list of triggers.
CREATE VIEW [owner.]view_name [(colname1 [,colname2 [,...]])]
[WITH ENCRYPTION | SCHEMABINDING | VIEW_METADATA]
AS select_statement
[WITH CHECK OPTION]
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 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 InterSystems SQL using the default of CASCADE.
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 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 syntax is parsed to provide compatibility with MSSQL. No functionality is provided.
Deletes rows of data from a table. Both
DELETE and
DELETE FROM are supported. Most other options are supported, with the following exceptions:
-
-
-
-
-
only very simple theta joins are supported (the
FROM table clause is transformed into nested subqueries)
-
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.
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:
-
EXECUTE as a value clause
-
-
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.
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:
-
-
-
only very simple theta joins are supported (the
FROM table clause is transformed into nested subqueries)
-
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 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.
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 specifies the number of rows to retrieve. InterSystems 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 InterSystems 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:
-
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.
-
-
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). InterSystems TSQL does not support the ^ wildcard. A LIKE comparison can include an ESCAPE clause, such as the following:
WHERE CategoryName NOT LIKE 'D\_%' ESCAPE '\'.
-
-
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()
JOIN (equivalent to INNER JOIN), INNER JOIN, and LEFT JOIN supported. Parentheses can be used to rationalize parsing of multiple joins.
A union of two (or more)
SELECT statements is supported. InterSystems 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 followed by any qualifier other than
NEXT (the qualifier can be omitted).
-
Note that
DEALLOCATE is supported, but that, by design, it generates no code.
Optimizes query access for a specified table. InterSystems IRIS 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().
The
IF command is supported with four syntactic forms:
IF condition
statement
[ELSE statement]
IF...THEN...ELSE single-line syntax:
IF condition THEN statement [ELSE statement]
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()
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.
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.
InterSystems 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.
InterSystems TSQL supports both forms of the
WAITFOR command:
WAITFOR DELAY timeperiod
WAITFOR TIME clocktime
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.
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.
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:
-
-
-
SET IDENTITY_INSERT Permitted values are
SET IDENTITY_INSERT ON and
SET IDENTITY_INSERT OFF. If ON, an INSERT statement can specify an identity column value. This variable applies exclusively to the current process and cannot be set on linked tables. Therefore, to use this option you should define a procedure in TSQL to perform both the SET IDENTITY_INSERT and the INSERT, then link the procedure and execute the procedure in InterSystems IRIS via the gateway.
-
SET NOCOUNT Permitted values are
SET NOCOUNT ON and
SET NOCOUNT OFF. When set to ON, messages indicating the number of rows affected by a query are suppressed. This can have significant performance benefits.
-
-
SET ROWCOUNT Set to an integer. Affects subsequent SELECT, INSERT, UPDATE, or DELETE statements to limit the number of rows affected. In a SELECT statement, ROWCOUNT takes precedence over TOP: if ROWCOUNT is less than TOP, the ROWCOUNT number of rows is returned; if TOP is less than ROWCOUNT, the TOP number of rows is returned. ROWCOUNT remains set for the duration of the process or until you revert it to default behavior. To revert to default behavior,
SET ROWCOUNT 0. If you specify a fractional value, ROWCOUNT is set to the next larger integer.
-
The following
SET variables are parsed, but ignored:
InterSystems TSQL provides support for transactions, including named transaction names. It does not support savepoints. Distributed transactions are not supported.
Supported for the following forms only:
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.
The
CHECKPOINT statement is parsed but ignored in InterSystems TSQL. It performs no operation.
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.
The following standard Transact-SQL statements are supported.
Creates a named executable procedure.
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 an InterSystems IRIS type descriptor. Currently, result sets and tables can't be returned.
-
-
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.
-
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:
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.
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.
Supported. The WITH EXECUTE keyword clause is supported.
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.
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 ("").
Executes a procedure, optionally passing in parameters.
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
The following example executes the InterSystems IRIS-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)
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.
CREATE USER creates a new user ID and password. Executing this statement creates an InterSystems IRIS User with its password set to the specified user name.
User names are not case-sensitive. InterSystems TSQL and InterSystems SQL both use the same set of defined user names.
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 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() 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
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).
TSQL supports a number of InterSystems extensions to Transact-SQL. To allow for the inclusion of these InterSystems-only statements in portable code, InterSystems 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 InterSystems TSQL. For further details, refer to the Comments section of the
TSQL Constructs chapter of this document.
TSQL includes the following InterSystems extensions:
This extension allows you to include ObjectScript code in the compiled output. It takes one or more lines of 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 InterSystems SQL into TSQL code:
CACHE {&sql(SET TRANSACTION %COMMITMODE EXPLICIT)}
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 InterSystems IRIS cannot otherwise determine at import whether such a stored procedure is a query or not.