Stored Procedures
Summary
The Stored Procedures section explains what are the eLegere SQL Stored Procedure's parameters and how they behave according to the context.
The section includes:
- An explanation of the eLegere fixed Stored Procedure parameters.
- How to specify additional parameters and values.
- Differences in Stored Procedures' employment according to the context.
Where to Insert the Stored Procedures?¶
You must insert the Stored Procedures in the eLegere database.
Contact your eLegere installation's DBA (or a DBA delegate) to insert your Stored Procedure in the database.
Parameters¶
Learn what are the fixed parameters of eLegere Stored Procedures. (If you want to add further parameters to the Stored Procedure, check How to Add Additional Parameters in this document.)
SQL Parameters¶
1 2 3 4 |
|
By Default, the @OutCodeStatus
, @OutLevel
, and @OutMessage
are empty. If you assign a value to @OutCodeStatus
, @OutLevel
, and @OutMessage
, you can customize what message to display to the user (see below for more information).
@jsonParameters
parameter accepts a String containing JSON data as value. The JSON Object in the string contains the row's information to be added or modified, the Storage's table name, and the user' account launching the Stored Procedure. See JSON Object for more information about the object's structure.@OutCodeStatus
requires a status code. The status code specifies how eLegere must behave after the Stored Procedure's execution and what result reports. If not specified and left empty, the value passed is000200
(corresponding to "Success" result of the action). See Status Codes for the list of behaviors.@OutLevel
requires as value a String specifying the type of notification message to display:INFO
,ERROR
orSUCCESS
.INFO
: a notification message informs the user. (Blue-colored notification)ERROR
: a notification message informs that an error happened. (Red-colored notification)SUCCESS
: a notification message informs of the Stored Procedure's success. (Green-colored notification)
@OutMessage
requires as String value with the message's content to be displayed to the user.
JSON Object¶
Learn the structure of the JSON Object in the @jsonParameters
from SQL Parameters.
1 2 3 4 5 |
|
- The
Item
key in the JSON object contains the row's object with all the keys and values containing its information. - The
StorageName
key contains the eLegere Storage's SQL table Name. The name (i.e.[SQL TABLE NAME]
) corresponds to the Storage's physical name in eLegere. Corresponds to the table where the user is performing the action. UserId
is the logon's Username.
Status Codes¶
Follows a list of values that @OutCodeStatus
(see SQL Parameters) accepts.
Each code specifies a different behavior after the Store Procedure's execution that you can specify.
Code | Name | Behavior |
---|---|---|
000500 | Failed | eLegere blocks the whole database transaction and restores the previous table's status. Displays an error message. |
000400 | Success Despite Errors | eLegere blocks the whole database transaction and restores the previous table's status. Displays a "Success" message. |
000300 | Ignore | eLegere ignores the row. Displays a "Success" message. |
000200 | Success | eLegere displays a "Success" (This is the Default value for the @OutCodeStatus parameter.) |
How to Add Additional Parameters¶
Learn how to add additional parameters to a Stored Procedure in a eLegere.
Info
See How to Work Directly with the Database: Stored Procedures from the Designer Guide to learn about Stored Procedure type Custom Actions.
In Stored Procedures type Custom Actions, you can add parameters and values besides those described in SQL Parameters above. Click the New in the Custom Action's Parameters settings. The action enables to insert a new couple of parameters and values.
Use Context¶
Learn how Stored Procedures behave in each eLegere usage context.
- Custom Action with Event Trigger: The Stored Procedure Custom Action with Event Trigger is executed through a transaction since multiple Master and Detail rows can be involved. The JSON Object includes values for the
Item
andStorageName
keys for the row. - Custom Action with Manual Trigger: The Stored Procedure Custom Action runs directly on a selected row. So, the action is not performed through a transaction. The JSON Object includes values for the
Item
andStorageName
keys for the row. - Scheduled Job: No SQL Transaction nor
Item
values orStorageName
values from the JSON Object. The Scheduler runs the Stored Procedure outside an application.
Execution Context | Parameters | Parameters | |
---|---|---|---|
Transaction | Item |
StorageName |
|
Custom Action with Event Trigger | |||
Custom Action with Manual Trigger | |||
Scheduled Job |
Individual Transactions - Settings
Check the Individual Transactions settings of the application where the Stored Procedure will run.
In an eLegere application, you can decide whether:
- The application saves all the Master and all the Detail rows in a single transaction.
- The application saves each Master row and related Detail row through a separate individual transaction.
Which behavior depends on the Individual Transaction setting in the Design Mode. See Save the Rows in a Single Save Process or through Multiple Save Processes in the Designer Guide for more information about Individual Transactions settings.