--- schema_version: "1" id: df8115dff2fe36ac802923d7baa1bf1a538b621f5bfbc45d88cabc3c8adba937 title: Execute Stored Procedures Examples collection: mulesoft version: current locale: en-us portal_url: https://docs.salesforce.com/mulesoft/current/en-us/db-connector/1.15/database-stored-procedure.md canonical_url: https://docs.mulesoft.com/db-connector/1.15/database-stored-procedure source_site: docs.mulesoft.com content_type: documentation content_hash: 949e0212cc5625e60f413d8e0bfb2e764d34c71238ada363e633f3310f051712 description: MuleSoft Documentation Site release: "1.15" latest: false --- --- product: Database Connector version: 1.15 --- # Execute Stored Procedures Examples > For the full documentation index, see: https://docs.mulesoft.com/llms.txt 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. > [!NOTE] Streaming of stored procedure result sets applies only when the JDBC driver supports > multiple open result sets (`DatabaseMetaData#supportsMultipleOpenResults()` returns `true`). If > the driver doesn’t support this capability (for example, the Microsoft SQL Server JDBC driver), > Database Connector reads every result set fully into memory before the operation returns. Large > result sets can cause high memory usage. 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`: ```xml 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: ```dataweave %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: ![Configuring dates on the Stored procedure operation](/mulesoft/current/en-us/db-connector/1.15/_images/database-stored-example-4.png) Figure 1. Dates on Stored Procedure configuration In the XML editor, the `` with the DataWeave transformation looks like this: ```xml { call createEmployee(:name, :age, :date) } ``` ## 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 `` and `` configuration looks like this: ```xml { call doubleMyInt(:myInt) } #[{'description' : payload}] ``` ## 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: ![The Stored procedure operation with the In out parameters field configuration](/mulesoft/current/en-us/db-connector/1.15/_images/database-stored-example-2.png) Figure 2. Stored procedure with In Out parameters configuration In the XML editor, the `` and `` configuration looks like this: ```xml { call doubleMyInt(:myInt) } ``` ## 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: ```xml { '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: ![The Stored procedure operation with the Input and Output parameters fields configuration](/mulesoft/current/en-us/db-connector/1.15/_images/database-stored-example-3.png) Figure 3. Stored procedure with Input and Output parameters configuration In the XML editor, the `` and `` configuration looks like this: ```xml { call multiplyInts(:int1, :int2, :result1, :int3, :result2) } #[{ 'int1' : 3, 'int2' : 4, 'int3' : 5 }] ``` ## 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: ```xml 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`. ![The Stored procedure operation with the Target Variable field configuration](/mulesoft/current/en-us/db-connector/1.15/_images/database-stored-example-5.png) In the XML editor, the `` configuration looks like this: ```xml ``` ### Calling Stored Procedures with Output Parameters When calling a stored procedure that returns an output parameter, ensure there is no space between the opening curly brace `{` and the return parameter name. The syntax must be `{:returnParameterName = call …​}` without any whitespace. This example illustrates calling a stored procedure with output parameters: ```xml CREATE PROCEDURE dbo.up_NOP_CreateAttachment @NOP_ID INTEGER, @Description VARCHAR(255), @DetailDescription VARCHAR(255), @DetailOriginalFileName VARCHAR(255), @FileExtensionTypeCD VARCHAR(10), @Attachment VARBINARY(MAX) AS BEGIN -- Procedure logic here RETURN @returnCode END ``` To configure the stored procedure operation: 1. In the **Stored procedure** operation configuration screen, set **SQL Query Text** to: `{:returnCode = call dbo.up_NOP_CreateAttachment(:NOP_ID, :Description, :DetailDescription, :DetailOriginalFileName, :FileExtensionTypeCD, :Attachment)}` > [!IMPORTANT] Do not include a space between the opening curly brace `{` and the colon `:` > before the return parameter name. The syntax `{ :returnCode = …​}` with a space causes an > error: "Unable to detect function 'name'". Use `{:returnCode = …​}` without the space. 2. Set the **Input parameters** field to: ```dataweave { 'NOP_ID' : vars.metadataContent.NOPID as Number, 'Description' : vars.metadataContent.Description, 'DetailDescription' : vars.metadataContent.DetailDescription, 'DetailOriginalFileName' : vars.metadataContent.DetailOriginalFileName, 'FileExtensionTypeCD' : vars.metadataContent.FileExtensionTypeCD, 'Attachment' : payload as Binary } ``` 3. Set the **Output 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 `returnCode` and the **Type** field to `INTEGER`. In the XML editor, the `` configuration looks like this: ```xml {:returnCode = call dbo.up_NOP_CreateAttachment(:NOP_ID, :Description, :DetailDescription, :DetailOriginalFileName, :FileExtensionTypeCD, :Attachment)} #[{ 'NOP_ID' : vars.metadataContent.NOPID as Number, 'Description' : vars.metadataContent.Description, 'DetailDescription' : vars.metadataContent.DetailDescription, 'DetailOriginalFileName' : vars.metadataContent.DetailOriginalFileName, 'FileExtensionTypeCD' : vars.metadataContent.FileExtensionTypeCD, 'Attachment' : payload as Binary }] ``` ## See Also - [Database Connector Examples](https://docs.mulesoft.com/db-connector/1.15/database-connector-examples) - [Salesforce Help](https://help.salesforce.com)