Build an SQL Procedure

An SQL Procedure can be used to pull data into a digital document.

Task at hand

Create a SQL procedure and use it in a digital document with the following structure:

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
WHERE PersonalIdentificationNumber is not null
GO;

Before you begin

• Create the entity Account with the following attribute Personal identification Number type whole number.
• Insert a data form into the Portal for this entity and populate the attribute above with records.

1 Create the SQL Procedure

Navigate to the Microsoft SQL Server Management Studio. Open the database that is related to the FintechOS Studio.

Open the data base, click on Programability, left-click on Stored procedure, and create a new one.

CREATE OR ALTER PROCEDURE customersPIN
AS
BEGIN
SELECT * FROM ebs.FTOS_AB_Account
WHERE PersonalIdentificationNumber is not null
END
GO

Execute the procedure. As you can see in the photographs, the query is executed, and it returns the records which have the attribute personal Identification Number populated.

2 Create the DB Task

Follow the path: FintechOS Studio > Advanced > DB Task.

The DB Tasks List page appears.

At the top-right corner of the page, click the Insert icon. The Add DB Task page appears.

Type a Name for the DB task. It is the name used by the system.

In the Statement field, type the name of the stored SQL procedure, e.g. customersPIN.

Click the Save and reload tab. The security grid opened. Add a the security role created which has all the CRUD permissions on the entity FTOS_AB_Account.

For details, see DB Task.

2 Create the document

Follow the path: FintechOS Studio > Fintech Automation> Digital Documents.

Click Insert in the page to add a new document.

Enter a Name for the digital document.

Selected Data Source Type to be is Stored SQL Procedure.

In the Stored procedure field, provide the name of the SQL procedure following this convention: customersPIN fa9eaf4f-e65c-43f3-a176-63b2ff7b5c2e FTOS_AB_Account @UserId. The user id is unique for each user, therefore use you ID. The same is valid for Entity Name ID.

To get those two IDs in the console run the functions ebs.getCurrentEntityID and ebs.getUserID.

In the Template field, upload the document template to be used. The document will be uploaded into FintechOS Studio.

Click the Save and reload button.

For more details, see Digital Documents.

Congratulations! You have used the SQL Procedure in a document.