SQL Stored Procedures

For the uninitiated, Stored Procedures are a series of predefined SQL statements that are configured and stored in the database, and can be easily called from another application, such as Ignition, with a single statement. Conceptually, they are very similar to a scripting function: parameters may be passed to them, they can utilize looping and conditional logic, and can return a value.

Stored Procedure Syntax

Stored procedures are created and maintained in your database. As a result, the creation of a stored procedure is outside the scope of the Ignition User Manual: the commands used to create a stored procedure vary per database.

In regard to calling a Stored Procedure, the syntax can also differ.

SQL Server

The EXEC command is utilized to execute a stored procedure.

Pseudocode - Executing a Stored Procedure in SQL Server

EXEC dbo.myStoredProcedure

Parameters may be passed to the Stored Procedure. SQL Server's documentation has more details on utilizing parameters with Stored Procedures.

Pseudocode - Executing a Stored Procedure in SQL Server with Parameters

EXEC dbo.myStoredProcedure @myParam = 100, @AnotherParameters = 'Hello'

MySQL

MySQL uses the CALL command to execute a Stored Procedure. Note the parentheses characters at the end, as they must be present even when the Stored Procedure is not utilizing parameters.

Pseudocode - Executing a Stored Procedure in MySQL

CALL myStoredProcedure()

If parameters are defined, they can be passed via the parenthesis, similar to how functions work in Python.

Pseudocode - Executing a Stored Procedure in MySQL with Multiple Parameters

CALL myStoredProcedure(100, 'Hello')

For information on the creation of a stored procedure, as well as proper SQL syntax to call a Stored Procedure, reference your database's documentation. Alternatively, if you have a database administrator, they can typically help with the creation and execution of a Stored Procedure.

Calling Stored Procedures in Ignition

There are several locations in Ignition where Stored Procedures may be utilized from.

SQL Query Bindings

Instead of typing a query directly into a SQL Query binding, a Stored Procedure may be executed instead. Assuming a MySQL database contains a Stored Procedure named 'return_all_bays', we can call the procedure on a binding with the following:

SQL - Calling a Stored Procedure in MySQL

CALL return_all_bays()

As with all bindings, Tag and Property references may be utilized by clicking the appropriate icons in the binding window.

Named Queries

Named Queries support Stored Procedure calls. As mentioned under Stored Procedure Syntax, the syntax is based entirely on how your database expects a Stored Procedure to be called. Be mindful of the Query Type setting, as it has to match what the stored procedure is doing: if it is returning a result set, leave it set to Query, if it is modifying a record in the database, then set the type to Insert Query.

Stored Procedure Groups

One of the easiest ways to utilize Tags with a Stored Procedure is to use the Stored Procedure Group. Parameters can be easily assigned to each item in the group, and utilize all of the features of a Transaction Group, such as scheduled execution and triggers.

Each item in the group is linked to a specific parameter in the Stored Procedure. Any IN or INOUT parameters can write directly to the Tags, while new values can be fed into OUT and INOUT parameters allowing you to easily move data from Tags into the database with the Stored Procedure.

Stored Procedures in Python Scripts

There are a few ways to call a Stored Procedure from a script in Ignition.

Using createSProcCall and execSProcCall

The recommended approach to calling a Stored Procedure from a Python script in Ignition typically involves two main steps:

1. Calling system.db.createSProcCall to create a call context, or object that effectively represents the impending stored procedure call. This object can be used to specify parameter values that will be passed to the Stored Procedure.
2. Using system.db.execSProcCall to execute the Stored Procedure.

Once the Stored Procedure has been executed, the call context generated in step #1 can be used to reference any values that were returned.

Other System Functions

Technically, most other system functions in the "db" library, such as system.db.runPrepQuery, can be used to call a Stored Procedure. We generally recommend against this, as system.db.createSProcCall and system.db.execSProcCall are better suited to work with Stored Procedures and have some additional functionality not found in the other db functions.