Execute SQL stored procedure

You can use this business rule to extract information from an external SQL database into a FlowForma form, or to update information in the SQL database with values entered in a FlowForma form. This is achieved using SQL stored procedures. The user setting up this rule must be authorised to update tables in the SQL database.

Title

The first part of the business rule lets you define a title for your rule and select the event on which it operates. The default title is the standard FlowForma rule name; change it by deleting the text and entering your own. In the event field you determine when the rule is executed. For a flow this can occur when the form is loaded, started, or completed. For a step this can occur when the step is started, saved, or completed. For questions it can occur when the question is updated.

Rule condition(s)

The second part of the business rule lets you set conditions. These are not required, but if entered they control when the rule is executed in relation to other items in the form.

Rule actions

Service region: select the region that hosts the service processing the SQL connection: Europe, North America, Australia, or India.
Connection method: choose Global connections to select an existing connection set up in FlowForma settings, or Connection details below to enter the connection details directly in this rule. If you choose Connection details below, complete the Server, Database, SQL user name, and Password fields described below.
Server: enter the location of the server where the SQL database is stored.
Database: enter the name of the database. This must be a SQL database.
SQL user name: enter the user name of a user authorised to update tables in the database above.
Password: enter the password that corresponds to the user name.
Connection: click Connect to establish a connection to the database. If successful, the stored procedure drop-down is populated.
Stored procedures: select the stored procedure you want to use. Available after the connection is established.
Map table data to repeating table: check this to return the result of your stored procedure into a repeating table. The first column coming back from the database is Column 0.
Input mappings: map fields in the FlowForma form to fields in the database. In the example below, values entered into the six questions on the form are mapped to variables in the selected stored procedure. Depending on how the procedure is designed, these values could create, update, or remove items in the database.
Output mappings: retrieve information from fields in the database to return values that can be used in fields in the FlowForma form.

Stop execution: if stop execution is enabled and this rule is executed, no other rules are executed.
Enabled: rules aren’t saved to a list like questions and steps are, so if you want to stop a rule executing without deleting it, toggle this option to enable or disable the rule.
Enable audit: if checked, a record is added to the audit trail when this rule is executed. This option is available when the Compliance feature pack is activated.

There must be at least one stored procedure in the database, or the connection will fail.

Simple stored procedures (examples)

Insert record

USE [R4_Test_database]
GO
/****** Object: StoredProcedure [dbo].[InsertEmployeeRecord]   Script Date: 01/04/2015 13:39:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Use CREATE if you're adding for the first time, use ALTER if you're updating the record
ALTER PROCEDURE [dbo].[InsertEmployeeRecord]
(
    @EmpNo  nvarchar(20),
    @EmpFN  nvarchar(30),
    @EmpSN  nvarchar(30),
    @EmpAdd nvarchar(Max),
    @EmpAge numeric(3,0),
    @EmpGen nvarchar(20)
)
AS
SET NOCOUNT OFF;
INSERT INTO [dbo].[EmployeeDetails]
(
    [EmployeeNo],
    [FirstName],
    [SecondName],
    [EmpAddress],
    [EmpAge],
    [Gender]
)
VALUES
(
    @EmpNo,
    @EmpFN,
    @EmpSN,
    @EmpAdd,
    @EmpAge,
    @EmpGen
);

Update record

USE [R4_Test_database]
GO
/****** Object: StoredProcedure [dbo].[UpdateEmployeeRecord]   Script Date: 01/04/2015 13:43:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UpdateEmployeeRecord]
(
    @EmpNo  nvarchar(20),
    @EmpFN  nvarchar(30),
    @EmpSN  nvarchar(30),
    @EmpAdd nvarchar(MAX),
    @EmpAge numeric(3,0),
    @EmpGen nvarchar(20)
)
AS
SET NOCOUNT OFF;
UPDATE [dbo].EmployeeDetails
SET
    [FirstName]  = @EmpFN,
    [SecondName] = @EmpSN,
    [EmpAddress] = @EmpAdd,
    [EmpAge]     = @EmpAge,
    [Gender]     = @EmpGen
WHERE
    [EmployeeNo] = @EmpNo

Get record

USE [R4_Test_database]
GO
/****** Object: StoredProcedure [dbo].[GetEmployeeRecord]   Script Date: 01/04/2015 13:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetEmployeeRecord]
(
    @EmpNo  nvarchar(20),
    @EmpFN  nvarchar(30) OUTPUT,
    @EmpSN  nvarchar(30) OUTPUT,
    @EmpAdd nvarchar(MAX) OUTPUT,
    @EmpAge numeric(3,0) OUTPUT,
    @EmpGen nvarchar(20) OUTPUT
)
AS
SET NOCOUNT ON;
SELECT
    @EmpFN  = FirstName,
    @EmpSN  = SecondName,
    @EmpAdd = EmpAddress,
    @EmpAge = EmpAge,
    @EmpGen = Gender
FROM
    EmployeeDetails
WHERE @EmpNo = EmployeeNo
RETURN

Delete record

USE [R4_Test_database]
GO
/****** Object: StoredProcedure [dbo].[DeleteEmployeeRecord]   Script Date: 01/04/2015 13:45:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DeleteEmployeeRecord]
(
    @Original_ID nvarchar(20)
)
AS
SET NOCOUNT OFF;
DELETE FROM [dbo].EmployeeDetails
WHERE
    ([EmployeeNo] = @Original_ID);

​Title

​Rule condition(s)

​Rule actions

​Simple stored procedures (examples)

​Insert record

​Update record

​Get record

​Delete record

Title

Rule condition(s)

Rule actions

Simple stored procedures (examples)

Insert record

Update record

Get record

Delete record