Skip to content

Stored Procedures

Abstract

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
@jsonParameters NVARCHAR(MAX), 
@OutCodeStatus NVARCHAR(4000) OUTPUT, 
@OutLevel NVARCHAR(4000) OUTPUT, 
@OutMessage NVARCHAR(4000) OUTPUT

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 is 000200 (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 or SUCCESS.
    • 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
{
  "Item": {},
  "StorageName": "[SQL TABLE NAME]",
  "UserId": "j.doe@vesenda.com",
}
  • 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 Stored 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 and StorageName keys for the row.
  • Custom Action with Manual Trigger: The Stored Procedure Custom Action runs directly on a selected row. So, the action isn't performed through a transaction. The JSON Object includes values for the Item and StorageName keys for the row.
  • Scheduled Job: No SQL Transaction nor Item values or StorageName 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.

Database, Event Triggers, and Attachments

If you setup a Invoke Stored Procedure Custom Action managing attachments, consider the following behavior for the PRE ATTACHMENT INSERT and POST ATTACHMENT INSERT Event Triggers:

  • PRE ATTACHMENT INSERT: The application sends to the database only the attachment's info; e.g. Name, Type, path where the attachment will be stored. At this stage, you can still interrupt the operation since nothing is saved yet.
  • POST ATTACHMENT INSERT: The application has uploaded the attachment on the database. Also, the application provides the attachment's Id field's value. Note that you can't revert to the previous database's state at this point.

Tutorials