Using Stored Procedure in Flows
  • 15 Feb 2024
  • 2 Minutes to read
  • Dark
    Light

Using Stored Procedure in Flows

  • Dark
    Light

Article summary

Feature Details

Modified in Version

8.18

Location

System → Integration → Databases

Stored Procedures are queries that run in MSSQL with an EXEC command. Stored Procedures can perform queries on the database table to update, add, delete, select, or edit data. Stored Procedures can be integrated to create steps that can execute the Stored Procedure and move the input values from the Flow to the database table.

Although this document refers to MSSQL throughout, everything here applies to MYSQL.

Errors with MSSQL

Please partner with a database or system administrator or use internet resources to configure the Stored Procedure in MSSQL. If there are errors that occur while creating a Stored Procedure, please use Microsoft's Stored Procedures support documentation. Decisions cannot troubleshoot MSSQL operations outside of its relation to the Decisions platform. Ensure the testing environment is appropriate to proceed.

Prerequisites

  • MSSQL Database

PostgreSQL Integration

Decisions use Functions rather than Procedures for PostgreSQL when setting up a Stored Procedure for a PostgreSQL integration.


Adding Store Procedure to Decisions

In this example, a Stored Procedure has been created that adds a new person to the specified database table. To add the example store procedure, run the following SQL query in the connected database.

CREATE PROCEDURE AddPerson (
@PersonID text,
@FirstName text,
@Lastname text,
@DOB datetime) AS

Begin
	INSERT INTO dbo.person ([PersonID], [FirstName], [Lastname], [DOB])
			Values (@PersonID, @FirstName, @Lastname, @DOB)
			
			END
			GO
  1. Navigate to System (gear icon) → Integrations → Databases .

  2. From the top action panel, Click ADD PROCEDURE. On the Add Procedure window, choose the appropriate  Procedure Name from the drop-down menu. 

  3. The Procedure Definition Name will automatically populate. In the All Parameters window, inputs appear configured in the Stored Procedure, where the data type of the expected value also appears.

  4. If desired, select the Returns Data option to add input parameters for creating a Data Structure when running the Stored Procedure. Click OK. 


Store Procedure Steps Configurations

Store procedure steps are located in the Toolbox → Integration → Database → [DatabaseName] → Exec dbo.[StoreProcedureName]. The following table describes the settings available on the Store Procedure Steps.

Environment Setting

Description

Use Transaction

This setting allows you to manage transactions associated with the stored procedure execution.

  • Transaction Data: This is a new input exposed when you enable the setting that accepts two settings:

    • Commit Unresolved Transactions: This option determines whether unresolved transactions should be committed or rolled back.

    • Transaction Name: This field specifies the name of the transaction.

Run in Defined Environment

This setting, when enabled (as it is by default), the stored procedure will be executed in a predefined or default environment.

Run without Transaction

This setting is availbale in v8.18 and above.

If transactions are not needed for a specific stored procedure, this setting allows you to exclude them.

Override Connection String

This setting, when enabled, allows you to provide a custom database connection string instead of using the default connection.


Using Store Procedure Steps in a Flow

  1. In a Designer Project, click CREATE FLOW

  2. In the Toolbox panel, navigate to  INTEGRATION → DATABASE → [DATABASENAME] and attach the Exec dbo.AddPerson to the Start and End steps.

Debug

  1. On the top action bar, click Debug.

  2. Click START DEBUGGING.

  3. Open MSSQL and use a select statement or the menu command to show the updated database table. 

  4. The inputs mapped to the Exec Flow step were recorded to the database table as the Stored Procedure was called from the Flow.


Feature Changes

Description

Version

Developer Task

Added a new setting on the Store Procedure Steps to Run without Transaction

8.18

[DT-026523]


For further information on Integrations, visit the Decisions Forum.



Was this article helpful?

What's Next