# Execute Stored Procedure

### ExecuteStoredProcedure <a href="#executestoredprocedure" id="executestoredprocedure"></a>

**ExecuteStoredProcedure** runs a stored procedure on a database. It allows specifying values for parameters to send to the stored procedure and it returns the values of any out-parameters. If the stored procedure returns one or more result sets (tables), its rows can be received one at a time in a loop, or all at once in a list.

{% hint style="danger" %}
Important:\
Use the "Row by row" option of this function's Return options when retrieving large datasets!
{% endhint %}

***

### Properties <a href="#properties" id="properties"></a>

#### Connection type <a href="#connection-type" id="connection-type"></a>

The type of database driver to use to connect to the database.

The supported driver types are:

* SQL Server
* Oracle
* OLE DB
* ODBC

Alternatively, instead of creating a new connection, choose *Use transaction* to use a Transaction object from a [BeginTransaction](/reference/plugins/database/content/begintransaction.md) function.

#### Connection string <a href="#connection-string" id="connection-string"></a>

The [connection string](/reference/plugins/database/shared-properties/connectioneditor.md) that specifies how to connect to the database.

#### Transaction <a href="#transaction" id="transaction"></a>

Visible if [Connection type](#connection-type) is set to 'Use transaction'.

Allows to select the Transaction object from a [BeginTransaction](/reference/plugins/database/content/begintransaction.md) function.

#### Stored procedure <a href="#stored-procedure" id="stored-procedure"></a>

The name of the stored procedure on the database to call. Use the [Stored Procedure Editor](#stored-procedure) to discover all available stored procedures in the database.

Click on the ... icon to open the Stored Procedure Editor.

#### Stored Procedure Editor

**Stored procedure**

Based on the DB connection a list of stored procedures are returned. Select the stored procedure to call.

Parameters and result sets of the selected stored procedure are displayed.

<figure><img src="/files/faWRNAsLAnc3R8zqC9oD" alt="Stored Procedure Editor"><figcaption></figcaption></figure>

Select the relevant \*Update\*-checkboxes to indicate what to update when you click the SAVE button.

### Parameters

Define the collection of parameters that the stored procedure expects.

Each IN or IN/OUT parameter will appear as a property in the [Parameter values](#parameter-values) section.

Each IN/OUT, OUT, and Return value will be included as properties in the[ function's result set](#result-sets).

#### Timeout <a href="#timeout" id="timeout"></a>

Time (in seconds) to wait for the procedure to execute. 0 equals no timeout.

### Parameter values

This section will display when one or more parameters were defined with a direction of 'IN' or 'IN/OUT'.

**\[Parameter name]**

The value to pass for the \[Parameter name] parameter.

#### Result sets <a href="#result-sets" id="result-sets"></a>

The PL/SQL code of a stored procedure may contain 'SELECT' statements which cause a result set (table) to be returned by each one of these statements.

**Number of result sets**

The number of result sets returned by the stored procedure. You can click the ellipse (...) button to open the[ Stored procedure editor](#stored-procedure-editor) to try detect the result set fields from the database.

**Result set fields**

One property will appear for each result set for configuring its fields.

**Output option**

* *First row*\
  The function will return only the first row of each result set. If any result set returns no data, an error will be thrown.
* *First row, else empty row*\
  The function will return only the first row of each result set. When a result set returns no data, the function will return a row containing default values.
* *List of rows*\
  The function will return the rows of each result set in separate lists.
* *Row by row*\
  The function will return the rows of each result set one row at a time in a loop. This is recommended whenever you expect to retrieve many items, but you don't need the complete list of items all at once.

***

### Definition <a href="#definition" id="definition"></a>

Contains the values of all IN/OUT, OUT, and Return value parameters configured in the Parameters property.\
The definition also contains one property for each result set containing the data (unless the Output option was set to *Row by row*).

***

### Links <a href="#links" id="links"></a>

[Wikipedia: Stored procedure](http://en.wikipedia.org/wiki/Stored_procedure)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.linx.software/reference/plugins/database/content/execute-stored-procedure.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.