What is the difference between a View and a Stored Procedure in SQL, and when should you use each in an Appian application?
What is the difference between a View and a Stored Procedure in SQL, and when should you use each in an Appian application?
Answer
Many Appian apps never need either one. Record types with relationships handle most joins, and Write Records handles most saves. A database view helps when several screens need the same read-only data and record relationships can’t express it cleanly. A stored procedure helps when one action has to change several tables in dependent steps, and either all of them succeed or none do.
Quick comparison
View | Stored procedure | |
|---|---|---|
What it is | A saved SELECT that behaves like a read-only table | A saved block of SQL you call with parameters |
Reads or writes | Reads only | Both |
Parameters | None | IN, OUT and INOUT |
Logic | Joins, filters, calculated columns | Conditions, transactions, error handlers |
How Appian uses it | Source of a record type or data store entity | Execute Stored Procedure smart service, |
Typical use | Grids, record lists, reports | Approvals, bulk updates, parameterized reports |
Using a View
Example: Employee directory
The directory screen shows each employee with their department, cost center and manager. Managers are also rows in employee, so every grid and record view on that screen would otherwise repeat the same three-way join.
To use it in Appian, create a record type with the view as its source and set emp_id as the primary key. A view has no key of its own, so Appian relies on that column being unique. After that you query it like any other record type:
Common view mistakes
- Pointing Write Records at the view. Saves go to the base tables (
employee,department). The view is only for reading. - Expecting a synced record type to update by itself. Changes made outside Write Records only appear after the record syncs again. That includes computed columns like
years_of_service. - Using a plain
JOINfor optional relationships. TheLEFT JOINon manager keeps employees who have no manager. An inner join would silently drop them from the grid. - Skipping indexes. The database runs the view’s query every time it’s read, so index the join columns (
employee.dept_id,employee.manager_id). - Building a view Appian doesn’t need. If the grid only needs employee plus department, a many-to-one record relationship gives you the same data with no database object to maintain.
Using a stored procedure to write data
Example: Invoice Approval
Approving an invoice takes several steps:
- Confirm the invoice is still pending.
- Check that the approver’s limit covers the amount.
- Apply any active vendor discount.
- Update the invoice and write an approval history row.
If two approvers click at the same moment, or the history insert fails after the update, the data ends up wrong. The procedure locks the row, runs every step in one transaction and returns a result code.
Calling it from a process model
Use the Execute Stored Procedure smart service:
- Add custom inputs named
p_invoice_id,p_approverandp_comments. The name of each custom input must match the name of a corresponding stored procedure parameter. appian - Map the OUT values in custom outputs. Parameters can be accessed using the ac!parameters activity class parameter, so save
ac!parameters.p_result_codetopv!resultCodeandac!parameters.p_approved_amttopv!approvedAmount. appian - Add an XOR gateway on
pv!resultCode: SUCCESSgoes to a Sync Records node for the invoice, then to notifications.NOT_PENDINGandLIMIT_EXCEEDEDgo back to the user with a clear message.DB_ERRORgoes to your error path.
The result codes let the process tell business-rule failures apart from database errors. An already-approved invoice or an approver over their limit comes back as a value the gateway can branch on. Without result codes, the process only sees a failed node.
You can also call a procedure from an interface with a!executeStoredProcedureOnSave( dataSource, procedureName, inputs, timeout, onSuccess, onError ). For a write like this approval, a process model is usually the better choice: the gateway, the Sync Records step and the error path live in one place, and they show up in process history.
Using a stored procedure to read data
Example: vendor spend for a date range
Finance wants each vendor’s approved invoice total minus credit notes for a chosen period. A record type aggregation can’t easily express this, because it needs two date-filtered subtotals from different tables.
Calling it from an interface:
Limits to know before you rely on this function:
- Use it for reads only. Appian’s documentation warns: Since this function could run more than once, do not use it with stored procedures that modify data to avoid unintentional changes. appian
- It returns a Map data type with the following parameters: success (Boolean), error (Text), results (List of List of Map), parameters (List of Map). That’s why the example checks
successbefore building the grid. appian - The default maximum value for number of rows per result set is 1000. Result sets that exceed this threshold will be truncated. If the result could grow past that, add paging parameters to the procedure. appian
- The default timeout is 30 seconds. appian
- Name case depends on the database. For Oracle and Db2 databases, enter the name in uppercase. For PostgreSQL databases, enter the name in lowercase. appian
Choosing between them
- To show data in a grid or record list, start with a record type and relationships. Add a view when the joins or calculated columns get too awkward to express that way.
- To save data to one table, use Write Records.
- To change several tables together, with rules that must hold at the moment of the write, use a stored procedure called from a process model. Follow it with Sync Records for any synced record types it touched.
- For a parameterized read-only calculation, such as a date range or cutoff, use
a!executeStoredProcedureForQuery(). Try a record type aggregation first.
Neither a view nor a stored procedure is automatically faster than a standard Appian query. Speed depends on query design, indexes, data volume and execution plans.
Sources: