Caché Transact-SQL (TSQL) Migration Guide
Caché TSQL Constructs
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

Commonly Used Constructs
Table References
Caché TSQL supports table references with the Caché SQL format:
schema.table
The only mandatory table reference component is table.
Other forms of Transact-SQL may use table references with up to four components, separated by dots. Here is how a Transact-SQL table reference is processed:
For the purposes of name translation, a field name has the field suffix removed while translation is performed and then replaced afterwards.
Temporary Tables
Caché TSQL supports #tablename temporary tables. A #tablename temporary table is visible to the current procedure of the current process. It is also visible to any procedure called from the current procedure. #tablename syntax is only supported in TSQL procedures (class methods projected as procedures with language tsql).
A temporary table is defined by using CREATE TABLE with a table name starting with "#". The temporary table is created at runtime. A #tablename table definition goes out of scope when you exit the procedure. All temporary table definitions go out of scope when the connection is dropped. You can also explicitly delete a temporary table using DROP TABLE.
However, if a temporary table is referenced by an active result set, the temporary table may become invisible to the process, but the data and definition are retained until the result set goes out of scope.
A #tablename temporary table is visible both to the creating procedure and to any procedures called from that procedure. Temporary tables are visible to nested procedure calls. It is not necessary to declare the temporary table in the called procedure. If the called procedure also creates a temporary table with the same name, Caché uses the most recently created table definition. Because a temporary table is defined using a Caché local variable, the creation, modification, and deletion of these tables are not journaled transaction events; rolling back the transaction has no effect on these operations.
Transactions
Code generated for BEGIN TRAN, COMMIT and ROLLBACK uses explicit transaction mode, but following a transaction TSQL always restores the mode which was active before the BEGIN TRAN statement. TSQL restores this mode when the procedure is exited from, or when a COMMIT or ROLLBACK is issued, whichever comes first.
Cursor Name Management
You can declare the same cursor more than once, so long as only one version of the cursor is open at runtime. If the same cursor is declared more than once in a stored procedure, all but the first declaration are associated with renamed cursors. OPEN, FETCH, CLOSE, and DEALLOCATE statements are assumed to refer to the most recent DECLARE for the given cursor. Note that the lexical position of a statement within a stored procedure is all that is used to match up a cursor name with its DECLARE — no account is taken of runtime paths through the code.
Cursors inside queries are named using an extension of the scheme used in Caché SQL queries. For example:
DECLARE C CURSOR FOR SELECT A FROM B
--
OPEN C
FETCH C
CLOSE C
DEALLOCATE C
--
DECLARE C CURSOR FOR SELECT D FROM E
--
OPEN C
FETCH C
CLOSE C
DEALLOCATE C
Would be effectively translated to:
DECLARE C CURSOR FOR SELECT A FROM B
--
OPEN C
FETCH C
CLOSE C
DEALLOCATE C
--
DECLARE Cv2 CURSOR FOR SELECT D FROM E
--
OPEN Cv2
FETCH Cv2
CLOSE Cv2
DEALLOCATE Cv2
System Stored Procedures
sp_addtype
Supported, with the following limitations:
System Tables
System tables exist per Caché namespace.
Systypes
Partially supported.
SYSOBJECTS References
Commonly, an application will have setup procedures that create tables, views, and the metadata for the application environment. Such procedures will have expressions like:
IF EXISTS (SELECT * FROM SYSOBJECTS 
WHERE ID = OBJECT_ID('People'))
This determines if a table exists, in this example. It’s usually followed by a DROP and CREATE statement to reestablish the table metadata.
TSQL procedures and triggers can reference the SYSOBJECTS system table. Caché TSQL supports the following columns in the SYSOBJECTS table (%TSQL.sys.objects class properties):
name Object name.
id Object Id.
type Object type: can be one of the following values: K=PRIMARY KEY or UNIQUE constraint; P=stored procedure; RI=FOREIGN KEY constraint; S=system table; TR=trigger; U=user table; V=view.
deltrig Object ID of a delete trigger if the entry is a table. Table ID of a table if the entry is a trigger.
instrig Object ID of a table’s insert trigger if the entry is a table.
updtrig Object ID of a table’s update trigger if the entry is a table.
parent_obj Object identification number of parent object. For example, the table ID if a trigger or constraint.
schema Name of the schema in which the object resides.
parent_obj_name Object name of parent_obj. If parent_obj=0, parent_obj_name is the same as name.
The SYSOBJECTS table is read-only. The SYSOBJECTS table may be referenced from outside a TSQL procedure or trigger by the name %TSQL_sys.objects. SYSOBJECTS is not supported for tables mapped across namespaces.
Note:
Caché proves the %Dictionary package of class objects that can perform the same operations as SYSOBJECTS references. For further details, refer to the %Dictionary package in the InterSystems Class Reference.