Execute Stored Procedures Examples | MuleSoft Documentation
# Execute Stored Procedures Examples
Anypoint Connector for Database (Database Connector) **Stored procedure** operation invokes a stored procedure on the database. When the stored procedure returns one or more ResultSet instances, streaming is applied automatically to avoid preemptive consumption of the results, which can lead to performance and memory issues.
The following examples illustrates callable statements and dates on stored procedures, and how to invoke stored procedures by configuring the **Input parameters** , **Output parameters** , and **In out parameters** fields.
## Callable Statements in Stored Procedures
Database Connector supports the use of callable statements with the format:
`{ call procedureName(:param1, :param2, …​, :paramN) }`
Where `:paramN` matches the `Nth` parameter in the stored procedure declaration. Make sure that no whitespace is between the name of the stored procedure and the first parentheses. Even though all JDBC drivers must comply with the JDBC specification format, some drivers have their own specifications. Check the documentation of your JDBC driver for additional information on how to call the stored procedure. For example, for the Snowflake JDBC driver you must set the callable statement without the curly brackets `{` and `}`:
`call procedureName(:param1, :param2, …​, :paramN)`
### Passing Parameters to Stored Procedures
When passing parameters to a stored procedure:
  * Place all the parameters the store procedure contains, even optional parameters with default values.
  * Put the parameters in the same order as they were defined in the stored procedure.
  * Parameterize the parameters, do not hardcode strings, numbers, etc.


The following example illustrates an Oracle database that was initialized when creating a table named `SYSTEM.employees` and stored procedure named `createEmployee`:
```
  CREATE TABLE SYSTEM.employees(
    employee_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    employee_name VARCHAR2(100),
    employee_age INTEGER,
    employee_birthday TIMESTAMP,
    PRIMARY KEY(employee_id)
  );

  CREATE PROCEDURE createEmployee(e_name VARCHAR2, e_age NUMBER, e_birth_date DATE) AS
  BEGIN
    INSERT INTO SYSTEM.employees(employee_name, employee_age, employee_birthday) VALUES(e_name, e_age, e_birth_date);
  END;
```

Because Database Connector does not support named parameters in callable statements, attempting to match parameters by name does not work, for example:
`{ call createEmployee(e_age ⇒ :age, e_birth_date ⇒ :date, e_name ⇒ :name) }`
You must provide the parameters in the appropriate order, for example:
`{ call createEmployee(:name, :age, :date) }`.
## Use Dates on Stored Procedures
Database Connector does not support the use of engine-specific embedded functions. For example, if you want to change a date to a specific format before calling a procedure in an Oracle database, the following approach does not work:
`{ call createEmployee(e_name ⇒ :name, employee_age ⇒ :age, e_birth_date ⇒ TO_DATE(:date, 'YYYY-MM-DD HH:mm:ss')) }.`
Use DataWeave for all data transformations before the invocation to the callable statement. Using the previous example, perform the following transformation:
  1. In your Studio flow, select the **Stored procedure** operation.
  2. In the operation configuration screen, set the **SQL Query Text** field to the query to execute, for example:
`{ call createEmployee(:name, :age, :date) }`
  3. Set the **Input parameters** field to the following DataWeave code:


```
%dw 2.0
 output application/json
 fun format(d: DateTime) = d as String { format: "yyyy-MM-dd HH:mm:ss" }
 ---
 { 'date': format(|2019-10-31T13:00:00.000Z|), 'name': 'rick', 'age': 60 }
[source,xml,linenums]
```

The following screenshot shows the configuration in Studio:
Figure 1. Dates on Stored Procedure configuration
In the XML editor, the `<db:input-parameters>` with the DataWeave transformation looks like this:
```
<db:stored-procedure doc:name="Create Employee" config-ref="Database_Config">
  <db:sql>{ call createEmployee(:name, :age, :date) }</db:sql>
		<db:input-parameters>
		 	<![CDATA[#[%dw 2.0
				output application/json
				fun format(d: DateTime) = d as String { format: "yyyy-MM-dd HH:mm:ss" }
				---
				{ 'date': format(|2019-10-31T13:00:00.000Z|), 'name': 'rick', 'age': 60 }
			]]]>
		</db:input-parameters>
	</db:stored-procedure>
```

## Configure the Input Parameters Field in the Stored Procedure Operation
In the **Input parameters** field, specify values that create a map in which keys are the name of an input parameter to be set on the JDBC prepared statement. Reference each parameter in the SQL text using a colon prefix, for example, `where id = :myParamName`. The map’s values contain the actual assignation for each parameter. When giving values to the parameters:
  * Define each parameter name with single quotes for example, `'paramName': value`.
  * For default values either use DataWeave or a variable, so if you pass null, or an empty string the store procedure’s default value won’t be pushed by Mule runtime engine.
  * Enter input values with parameters only. Do not hardcode the values within the **Stored Procedure** operation.


To configure the **Input parameters** field:
  1. In your Studio flow, select the **Stored procedure** operation.
  2. In the operation configuration screen, set the **SQL Query Text** field to the query to execute, for example:
`{ call doubleMyInt(:myInt) }`
  3. Set the **Input parameters** field to `{'myInt' : payload}`.


In the XML editor, the `<db:stored-procedure>` and `<db:input-parameters>` configuration looks like this:
```
<db:stored-procedure config-ref="dbConfig">
  <db:sql>{ call doubleMyInt(:myInt) }</db:sql>
  <db:input-parameters>
    #[{'description' : payload}]
  </db:input-parameters>
</db:stored-procedure>
```

## Configure the In Out Parameters Field in the Stored Procedure Operation
In the **In out parameters** field, specify values that create a map in which keys are the name of a parameter to be set on the JDBC prepared statement which is both input and output. Reference each parameter in the SQL text using a colon prefix, for example, `where id = :myParamName`. The map’s values contain the actual assignation for each parameter.
To configure the **In out parameters** field:
  1. In your Studio flow, select the **Stored procedure** operation.
  2. In the operation configuration screen, set the **SQL Query Text** field to the query to execute, for example:
`{ call doubleMyInt(:myInt) }`
  3. Set the **In out parameters** field to `Edit inline`.
  4. Click the plus sign (**+**) to add a new value.
  5. In the new window, set the **Key** field to `myInt` and the **Value** field to `3`.


The following screenshot shows the configuration in Studio:
Figure 2. Stored procedure with In Out parameters configuration
In the XML editor, the `<db:stored-procedure>` and `<db:in-out-parameter>` configuration looks like this:
```
<db:stored-procedure config-ref="dbConfig">
 <db:sql>{ call doubleMyInt(:myInt) }</db:sql>
 <db:in-out-parameters>
   <db:in-out-parameter key="myInt" value="3"/>
 </db:in-out-parameters>
</db:stored-procedure>
```

## Configure the Input and Output Parameters Fields in the Stored Procedure Operation
In the **Output parameters** field, specify a list of values to be set on the JDBC prepared statement. Reference each parameter in the SQL text using a colon prefix, for example, `call multiply(:value, :result)`.
To configure the **Input parameters** and **Output parameters** fields:
  1. In your Studio flow, select the **Stored procedure** operation.
  2. In the operation configuration screen, set the **SQL Query Text** field to the query to execute, for example:
`{ call multiplyInts(:int1, :int2, :result1, :int3, :result2) }`
  3. Set the **Input parameters** field to:
```
{
  'int1' : 3,
  'int2' : 4,
  'int3' : 5
}
```

  4. Set the **Output parameters** field to `Edit inline`.
  5. Click the plus sign (**+**) to add a new value.
  6. In the new window, set the **Key** field to `result1` and the **Type** field to `INTEGER`.
  7. Repeat the steps 5 and 6 to add a new value, set the **Key** field to `result2` and the **Type** field to `INTEGER`.
## Configure the Input and Output Parameters Fields in the Stored Procedure Operation
In the **Output parameters** field, specify a list of values to be set on the JDBC prepared statement. Reference each parameter in the SQL text using a colon prefix, for example, `call multiply(:value, :result)`.
To configure the **Input parameters** and **Output parameters** fields:
  1. In your Studio flow, select the **Stored procedure** operation.
  2. In the operation configuration screen, set the **SQL Query Text** field to the query to execute, for example:
`{ call multiplyInts(:int1, :int2, :result1, :int3, :result2) }`
  3. Set the **Input parameters** field to:
```
{
  'int1' : 3,
  'int2' : 4,
  'int3' : 5
}
```

  4. Set the **Output parameters** field to `Edit inline`.
  5. Click the plus sign (**+**) to add a new value.
  6. In the new window, set the **Key** field to `result1` and the **Type** field to `INTEGER`.
  7. Repeat the steps 5 and 6 to add a new value, set the **Key** field to `result2` and the **Type** field to `INTEGER`.


The following screenshot shows the configuration in Studio:
Figure 3. Stored procedure with Input and Output parameters configuration
In the XML editor, the `<db:input-parameters>` and `<db:output-parameters>` configuration looks like this:
```
<db:stored-procedure config-ref="dbConfig">
  <db:sql>{ call multiplyInts(:int1, :int2, :result1, :int3, :result2) }</db:sql>
  <db:input-parameters>
    #[{
      'int1' : 3,
      'int2' : 4,
      'int3' : 5
    }]
  </db:input-parameters>
  <db:output-parameters>
    <db:output-parameter key="result1" type="INTEGER"/>
    <db:output-parameter key="result2" type="INTEGER"/>
  </db:output-parameters>
</db:stored-procedure>
```

## Returning a Result Set
When you call a stored procedure that returns a result set, the result set is assigned to the payload by default. To change the returning result set, configure a **Target Variable** or a **Target Value** in the **Advanced** tab of the **Stored procedure** operation configuration.
The following example illustrates an MSSQL database that is initialized when creating a table named `languages` and a stored procedure named `myProc` that returns a result set:
```
CREATE TABLE LANGUAGES (NAME VARCHAR(128), SAMPLE_TEXT VARCHAR(max));
CREATE PROCEDURE myProc AS
  SELECT * FROM LANGUAGES l;
RETURN;
```

  1. In the **Stored procedure** operation configuration screen, set **SQL Query Text** to `{ call myProc() }`.
  2. In the **Advanced** tab, set **Target Variable** to `aResult`.


In the XML editor, the `<db:stored-procedure>` configuration looks like this:
```
<db:stored-procedure config-ref="MsSQL" target="aResult">
 <db:sql ><![CDATA[{ call myProc() }]]></db:sql>
</db:stored-procedure>
```

## See Also
  * Database Connector Examples
  * MuleSoft Help Center