Using Stored Procedures

Updated on

On each Sonic Forms page you can set a stored procedure to be executed on form submission. Sonic Forms will save all the user input first and then, if successful, call the stored procedure allowing you to use the stored procedure for post processing of any kind of data update or transformation based on the information provided by a user.

Stored procedures that can be used with Sonic Forms must have the following parameters (although they do not all have to be used by the procedure itself):
  • ID – ID of the iMIS user who submitted the form or on whose behalf the form was submitted.
  • SEQN – SEQN ID of the multi-instance record updated by the user (optional).
In order to set a stored procedure you need to open Sonic Forms Admin Panel and define the Stored Procedure to be executed:

Here is an example of a simple stored procedure using only the ID parameter (@IMISID):

You can use the return value to control whether the user will be redirected to the next form (if url is attached on the submit button). If the return value is 0 or greater than 0 Sonic Forms continues to the next page or displays the message on success (if no url is set). If the return value is negative or the stored procedure executes with errors the failure message will be shown and no redirect will occur. This is useful if you need to validate user input, check if the logged in user has permissions to continue, etc.

Here is an example:

If you have fields from multi-instance tables on the page you can pass the sequence number of the created/updated record to the stored procedure. In order to do this you need to put the table name in the SEQN table field and update your stored procedure to accept two parameters: @IMISID and @SEQN. See the example below:

1. In Sonic Forms Admin panel put name of the table in SEQN table field.

2. Make sure your stored procedure has two parameters.