Skip to main content

TSQL System Stored Procedures

InterSystems IRIS® data platform provides TSQL system stored procedures to help you administer and track database objects in your system. TSQL system stored procedures can be executed in any namespace and schema. The scope of all stored procedures is the current namespace.

Stored procedures are executed using the TSQL EXECUTE or EXEC command. For a stored procedure with a name such as sp_xxx, this execute command can be explicit or implicit. Thus the following TSQL statements are functionally identical:

EXECUTE sp_addtype 'shortstr','varchar(6)','not null'
EXEC sp_addtype 'shortstr','varchar(6)','not null'
sp_addtype 'shortstr','varchar(6)','not null'

InterSystems TSQL supports the following system stored procedures:

sp_addtype

This system stored procedure adds a user-defined data type.

sp_addtype typename, phystype [(length) | (precision [, scale])]
    [, "identity" | nulltype]

typename is a user-defined data type name. phystype is the physical datatype on which to base the user-defined datatype. You can optionally specify either IDENTITY or nulltype. The optional IDENTITY keyword specifies that the user-defined data type has the IDENTITY property, which by definition is NOT NULL. The optional nulltype specifies whether a column with this data type should have a NOT NULL constraint: options are NULL (allow nulls) and NOT NULL or NONULL (do not allow nulls).

A typename can only be used for table columns. A typename is defined within the current namespace; mapping of a typename across namespaces is not supported.

The SQL compiler looks up type mapping in the DDL Datatype Mapping definitions in the system configuration before looking up in the typename table. Therefore, if you define a typename using sp_addtype named My_Type, but you also have a My_Type defined in the User DDL Datatype Mappings, the mapping of My_Type comes from the User DDL Datatype Mapping definition.

This lookup of typename is done at runtime, so typename does not need to be defined at DDL compile time.

A data type defined using sp_addtype with a specified nulltype behaves as follows:

  • If the DDL field specifies NULL, a value for the field is not required, even if sp_addtype specified NOT NULL.

  • If the DDL field specifies NOT NULL, a value for the field is required, even if sp_addtype specified NULL.

  • If the DDL field does not specify NULL or NOT NULL, a value for the field is required if sp_addtype specified NOT NULL.

  • If the DDL field does not specify NULL or NOT NULL, a value for the field is not required if sp_addtype either specified NULL or did not specify a nulltype.

The following example creates the data type shortstr, which requires a value (NOT NULL) and this value must be six or less characters:

EXEC sp_addtype 'shortstr','varchar(6)','not null'

sp_droptype

This system stored procedure deletes a user-defined data type.

sp_droptype typename

typename is a user-defined data type name defined within the current namespace.

See sp_addtype.

sp_procxmode (Sybase only)

This system stored procedure displays or changes the execution mode associated with stored procedures.

sp_procxmode [procname [, tranmode]]

procname is the name of a stored procedure. tranmode is the transaction execution mode: Values are "chained", "unchained" (the default), and "anymode".

When called with no arguments, sp_procxmode returns a result set of all procedures defined for the namespace, their user name and transaction mode (1=unchained). When called with just the procname argument, sp_procxmode returns the user name and transaction mode of the specified procedure.

The following tranmode values are supported:

  • Chained: implicitly begins a transaction before any data-retrieval or modification statement: delete, insert, open, fetch, select, or update. You must still explicitly end the transaction with commit transaction or rollback transaction. A procedure defined to run in chained mode will set autocommit_off at the beginning of the procedure, and restore the prior setting at the end of the procedure.

  • Unchained: (the default) requires the user to explicit begin a transaction before any data-retrieval or modification statement. You must explicitly end the transaction with commit transaction or rollback transaction. A procedure defined to run in unchained mode will set autocommit_on at the beginning of the procedure, and restore the prior setting at the end of the procedure.

  • Anymode: If the mode is not defined, or if it is defined as anymode, no change is made to the autocommit setting.

InterSystems IRIS does not support a SET [UN]CHAINED option to change process settings. The setting used is the current setting of the process's auto-commit mode.

InterSystems IRIS does not report an error if a procedure defined as chained is called by a process in autocommit_on mode, nor does it report an error if a procedure defined as unchained is called by a process in autocommit_off mode.

The tranmode metadata is not part of the actual method definition. This means that changing the tranmode does not require a recompile. It also means that when exporting/importing classes containing TSQL (Sybase) stored procedures, the tranmode setting for the procedure is not exported with the class definition. Upon import, if a procedure needs to be defined in Chained mode, you must call EXEC sp_procxmode 'procname', 'chained' for the procedure.

FeedbackOpens in a new tab