Calling Stored Procedure From Flow to Write Data

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.

Prerequisites

• MSSQL Database

Example

In this example, a Stored Procedure has been created that adds a new person to the specified database table. 

1. Navigate to System > Integrations > Databases. Select the connection name of the database and click ADD TABLE to add a table.
    
2. Click ADD PROCEDURE. On the Add Procedure window, choose the appropriate Procedure Name from the dropdown menu. 
3. The Procedure Definition Name will automatically populate. In the All Parameters window, inputs appear as they were configured in the Stored Procedure, where the data type of the expected value also appears. If desired, select the Returns Data option to add input parameters for creating a Data Structure when the Stored Procedure is run. Click OK. 
4. In a Designer Project, click CREATE FLOW. 
5. 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.