---
id: database--database-routine-call
title: "Routine call"
summary: "Calls a stored procedure or function on a database and emits its result: the return value, output parameters, or the rows of a table-valued / pipelined result. Input and output types are generated from the routine signature. Works inside a *Sql transaction* scope."
audience: user
keywords: "input, output, continue, connectionstring, typeprefix, procedurename, commandtimeout, keeporiginalnames, batchmode"
---
# Routine call

Calls a stored procedure or function on a database and emits its result: the return value, output parameters, or the rows of a table-valued / pipelined result. Input and output types are generated from the routine signature. Works inside a *Sql transaction* scope.

## Pins

| Pin | Kind | Types | Description |
|---|---|---|---|
| Input | input | generated `<flow namespace>.<TypePrefix>_<schema>_<routine>_Input` | One property per input (and in/out) parameter. |
| Output | output | generated `<TypePrefix>_<schema>_<routine>_Return` or the routine's user-defined return type | Return value and output parameters, or one record per returned row. |
| Continue | continue output | same as input | Called when the call produced nothing for `Output` (see Behaviour). |

## Configuration

| Field | Type | Default | Description |
|---|---|---|---|
| `ConnectionString` | database link name | | Required. Name of a repository database link (link selector). Looked up by exact name; a raw connection string or `#name` prefix is not resolved. |
| `TypePrefix` | string | `Db` | Prefix of the generated type names (an `_` is appended if missing). |
| `ProcedureName` | string (database explorer) | | Required. Routine name, `name`, `schema.name` or `schema.package.name` (Oracle). Without a schema the link's default schema is used. Triggers reconfigure. |
| `CommandTimeOut` | integer (seconds) | `20` | Command timeout. |
| `KeepOriginalNames` | boolean | `false` | `true` keeps parameter / field names; `false` converts them to C# names. |
| `BatchMode` | dropdown: `1` Auto, `2` Always, `3` Never | `1` | Whether the rows of one call (result set or returned collection) form a batch for batch consumers (Write file, Bulk insert, Build array, ...). `Auto`: only if the input record is not already in a batch (Timer, Http listener); a call per record of a batch keeps that batch. `Always`: a child batch per call. `Never`: no batch. |

The hidden field `OutputTypeName` is filled by reconfigure with the full name of the return type, and cleared when the routine has none.

## Behaviour

- Reconfigure (changing `ProcedureName`) reads the routine signature fresh from the database (never cached, so re-selecting the routine picks up changes), removes the types the component generated before and generates the types below. `<schema>_<routine>` is the qualified routine name with dots replaced by `_` (Oracle: `<schema>_<package>_<routine>`), converted to a C# name unless `KeepOriginalNames`. A database without stored routines shows an error: use *Sql query* there.
  - `..._Input` with one property per input and output parameter (database name kept as SQL name), set as the only type of `Input`;
  - for a scalar return value `..._Return` with property `ReturnValue` plus one property per output parameter; for a user-defined (object/record) return type that type itself; for procedures with output parameters only a `_Return` type with those parameters;
  - `<TypePrefix>_<type schema>_<type>` for each user-defined (object / record) parameter type, nested types included.
  The return type is set on `Output`. A component with its default name (or a `CAL_` name) is renamed to `CAL_<ProcedureName>`.
- Changing `TypePrefix` or `KeepOriginalNames` does not regenerate the types: re-select the routine.
- Per input record the routine is called once; parameter values are read from the input properties by their SQL name (`null` is sent as `DBNull`). A user-defined (object) parameter is sent as its record, field by field by SQL name, nested records included; a `null` record is sent as `null`.
- Results:
  - scalar return value: one `Output` record with `ReturnValue` and output parameters;
  - object return: one `Output` record mapped from the returned structure;
  - collection or table/pipelined result: one `Output` record per element/row; the rows form a batch (`BatchMode`) that ends after the last row was processed downstream and before `Continue`, an error fails it;
  - rows were streamed and output parameters have values: an additional `Output` record with the output parameters.
- `Continue` is only called in two cases: the routine has no generated return type (`OutputTypeName` empty) and returned nothing - `Continue` gets a `NoInputData` record; or rows were streamed and no output parameter has a value - `Continue` gets the input record. It is not a general "finished" signal.

## Notes

- The input record must have a property for every routine parameter (matched by SQL name); use the generated `_Input` type, for example built with a *Data mapper*. A missing property fails the call.
- When a routine returns no rows and has a return type, the result is handled as a return value: an `Output` record (possibly with empty values) is emitted instead of calling `Continue`. Procedures that only have output parameters go through this path too; test them before relying on the result shape.

## Example

```json
{ "ConnectionString": "MainDb", "ProcedureName": "dbo.GetCustomerBalance", "TypePrefix": "Db" }
```

Http listener → Data mapper (to `Db_..._Input`) → **Routine call** → Http response