Skip to main content
Version: 2023


Introduction to subroutines

Subroutines are used to define business logic that is not used directly by the user interface but is only called by other business logic or by third party applications, be it through the OData API or a message protocol independent webservice. Subroutines can be for instance functions, stored procedures or services.

Creating a subroutine

menu Business Logic > Subroutines > tab Form

Different types of subroutines are available, depending on the application platform.

PlatformTypeDescriptionCan be an API
SQLCLR functionCommon Language Runtime functionNo
SQLCLR procedureCommon Language Runtime procedureNo
SQLDLL assemblyAssembly LibraryNo
DB2External functionFunction that references an external programNo
DB2External procedureProcedure that references an external programNo
SQL, DB2, OracleFunctionSQL functionYes
SQL, DB2, OracleProcedureSQL stored procedureYes

Depending on the selected subroutine, the following return values can be specified:

  • None.

  • Scalar - The result is a single value. If you select this, the column Return scalar domain is displayed. A domain can be selected that determines the characteristics of the result value.

  • Table - The result is a table containing multiple rows. If this option is selected, the columns can be named in the Subroutine return columns tab. This will be displayed as the result in the table. These columns also refer to a domain.

The return values are not applicable for every program object.

Virtually every subroutine type has input parameters. These input parameters can be provided in the Subroutine parameters tab.


To view the object in the Code Overview screen, execute the task Go to object in Code overview go to overview. In the Code Overview, you can regenerate a program object's source code and execute it to the database. See also the Code overview guide.

Copy a subroutine

To make a copy of a subroutine:

menu Business logic > Subroutines > tab Settings

  1. Execute the Copy subroutine copy task.
  2. Enter a name for the copy in the field To subroutine.
  3. Select if you want to include Functionality assignments in the copy. If you deselect this option, the copied subroutine will not be assigned to any control procedure. Use this to create a bare object (the copied subroutine). Then, create a new control procedure, and assign the copied subroutine to it.

copy subroutine Copy a subroutine

Subroutine as API

Subroutines are generally used internally by the application and will not be accessible directly using Indicium. If a function or procedure has to be called by an external caller using Indicium, it can be marked as an API.


Operation-typed subroutines are always exposed as web services, not through Indicium but through a generated Java or C# SOAP service tier.

menu Business Logic > Subroutines > tab Form

  1. Check the API box to publish the subroutine as an API by Indicium. Use API (legacy) to publish it by Indicium Legacy.

  2. To submit a different API service name and parameter names: enter a Subroutine alias for subroutines and subroutine parameters. For example, if you don't want to use the Thinkwise naming convention (lowercase words separated by underscores) for your third party API.

    Indicium will now expose the procedure or function as a web service.

Atomic transaction

It is possible to start and complete transactions manually in code. However, you can also run a procedure-based subroutine as an atomic transaction. This setting provides a generic solution with nested transaction support and proper error handling.

The Atomic transaction setting for new tasks or subroutines is by default enabled if:

  • The model does not yet contain any tasks or subroutines.
  • The atomic transaction option has been enabled in the majority of the existing tasks or subroutines. If not, the default value is disabled.

If Atomic transaction is enabled, data changes will be handled as follows:

  • When the stored procedure uses a transaction, the data changes of each statement will be definitive once all statements within the task have run successfully. Errors will lead to a roll-back of the data changes made by earlier statements.
  • A stored procedure that uses a transaction can be called by a trigger or another stored procedure that also uses a transaction. In that case, the data changes will be definitive once the caller finishes the transaction. Rolling back the transaction at any point will roll back all the nested transactions.
  • When a stored procedure does not run in a transaction, data changes by each statement within the stored procedure are definitive the moment the individual statement has been executed (unless the caller opens a transaction).

To change the Atomic transaction setting:

Business logic > Subroutines > tab Settings > tab Subroutine

  1. Select or clear the Atomic transaction checkbox.

Not running a transaction is generally more performant since the database engine does not need to keep track of the data changes while the transaction is active. The data that is being tracked can also cause deadlocks as other transactions can be waiting to use this data.

However, not running in a transaction can cause data corruption, depending on how the code is written.

Subroutine options

menu Business Logic > Subroutines > tab Subroutine options

Depending on the platform of the subroutine and the type of subroutine, various options are available to change its behavior.

PlatformSubroutine typeSubroutine optionDescriptionOption values
SQLCLR functionDLL AssemblyThe ID of the DLL asssembly containing the CLR function.
SQLCLR functionEXECUTE_ASSpecifies the user account that must execute the CLR function.
SQLCLR functionRETURNS_NULL_ON_NULL_INPUTPerformance optimization that causes the function to return NULL when any of the parameters is null, without executing the function body.- Yes
- No
SQLCLR procedureEXECUTE_ASSpecifies the user account that must execute the CLR procedure.
SQL, OracleCLR procedureDLL AssemblyThe ID of the DLL asssembly containing the CLR procedure.
SQL, OracleDLL assemblyAssembly idThe ID of the DLL assembly.
SQL, OracleDLL assemblyDLL file locationThe physical DLL file location as seen from the database server used to create the DLL assembly in the database.
SQL, OracleDLL assemblyPermission setSpecifies to what extent an assembly can access external system resources such as files, the network, environment variables, or the registry.- SAFE
DB2FunctionDETERMINISTICSpecifies whether the function returns the same results each time that the function is invoked with the same input arguments.
DB2FunctionMODIFIES SQL DATASpecifies that the procedure can execute any SQL statement except statements that are not supported in procedures.
SQLFunctionEXECUTE_ASSpecifies the user account that must execute the subroutine. For example, SA specifies the system administrator user account.
SQLFunctionINLINEMarks a scalar function as inlinable. The generated function header will no longer use user-defined datatypes. Instead, it will use the 'raw' datatypes of the domains. The script to create the function will use the INLINE = ON clause to force it to become inlinable. This feature can speed up queries that use the function. Note that there are restrictions to keep in mind when writing logic for an inlinable function.- OFF
- ON
SQLFunctionRETURNS_NULL_ON_NULL_INPUTPerformance optimization that causes the function to return NULL when any of the parameters is null, without executing the function body.- No
- Yes
SQLFunctionSCHEMABINDINGBinds the function to the underlying objects. This may improve performance but causes the objects to become locked.- No
- Yes

inline subroutine option Example: using a subroutine option to mark a scalar function as inlinable

Was this page helpful?

Happy React is loading...