Using Stored Procedure in Flows

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

Postgres Integration

Decisions use Functions rather than Procedures for Postgres when setting up a Stored Procedure for a Postgres 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.

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 public.AddPendingJob 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