Hello PerfectApps Community,
Do you use connections to a database in your workflow application? PerfectApps can integrate with your database by calling on Stored Procedures. Some benefits of a Stored Procedure include being able to return data as you like, pull in data from multiple tables, and update data in multiple tables. By utilizing a Stored Procedure you can take advantage of using information from a relational database in your workflow or create a standalone data management application.
Although PerfectApps can return a dataset from a database, the standard functionality will perform a standard statement
. A standard statement will not include an instruction set on distinct returns, sorting parameters, or the ability to fish data from multiple tables in a single statement. This is where a Stored Procedure can come in handy.
For example, I call on a Stored Procedure to pull State Names and a State Codes from a table. Without a Stored Procedure I would pull back more than 20,000 returns in a random order. Receiving data back like that would be useless to me, so what I can do is clean it up by calling a Stored Procedure that will only return 50 states and 50 Codes in alphabetical order:
Example Statement:
SELECT DISTINCT
StateCode,StateName
FROM [ericsdb].[dbo].[ABCCO]
ORDER BY StateName ASC;
If the statement above looks like it needs a Rosetta stone you can still get the same job done with a little help. An IT with DB access should be able to create a Stored Procedure for you within 5 minutes if you can answer the following questions:
– What data do you need back?
– Is there data that should be excluded? (Whether it is a price, date, name, pretty much any condition)
– How do you need it back? (Do you want it sorted paired or named a certain way? If you want it named a certain way, be sure to let them know)
Be sure to let the IT know you will need back the following:
The Database Name, Procedure Name, Send Parameter Names, and Return Parameter Names. Once you receive the go ahead you can move on down to the configuration directions.
For those of you who are more familiar with SQL, you will be able to perform more complex functions such as joining data from tables running multiple inserts, or running functions. In PerfectApps running a complex Stored Procedure is the same as running a simple Stored Procedure. Performing a function within a Procedure can be used to integrate with other applications. Once you have your procedure configured you can move over to PerfectApps and enable your apps to call it by following the directions below.
Configuration Directions:
1. In PerfectApps, navigate to the Connections Dashboard
. Next, expand the Databases node from the tree menu and select the desired database connection. Click the Add Action button.
2. On the New Action page, assign a name to the action and select Stored Procedure as the Type.
3. In the Details area, enter the Procedure Name as it was named when it was created on the database server. For example, using the EXECUTE statement shown above the procedure name would be:
ericsdb.dbo.ABCCO
4. Parameters will use the procedure parameters as specified by their SQL names.
5. The result set columns are specified as Return parameters and should follow this naming convention: RS[index].ColumnName . (This is normally 1 unless specified as another. My example would be RS1.StateCode and RS1.StateName)
• RS specifies that this is a result set.
• [index] is the result set index. Note that there can be more than one result set returned by a Stored Procedure
• ColumnName is the name of the column inside the result set.
6. Click Test to verify the action was configured properly. On the Test Action screen, click Connect.
Once you have your procedure configured and tested, utilizing it is as easy as a regular connection
. Set your send parameters, select where you would like the information returned to and you are all set to integrate your application with your database. If you have additional questions please feel free to comment below. Thanks for reading and happy form building!
