Skip to main content
Question

What is the difference between a View and a Stored Procedure in SQL, and when should you use each in an Appian application?

A
Anonymous
December 21, 2025

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, a!executeStoredProcedureOnSave(), a!executeStoredProcedureForQuery()

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.

CREATE OR REPLACE VIEW vw_employee_directory AS
SELECT
e.emp_id,
CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
e.email,
e.job_title,
e.hire_date,
TIMESTAMPDIFF(YEAR, e.hire_date, CURDATE()) AS years_of_service,
e.status,
d.dept_id,
d.dept_name,
d.cost_center,
m.emp_id AS manager_id,
CONCAT(m.first_name, ' ', m.last_name) AS manager_name,
m.email AS manager_email
FROM employee e
JOIN department d ON d.dept_id = e.dept_id
LEFT JOIN employee m ON m.emp_id = e.manager_id
WHERE e.is_deleted = 0;


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:

a!queryRecordType(
recordType: recordType!EMP_EmployeeDirectory,
fields: {
recordType!EMP_EmployeeDirectory.fields.employee_name,
recordType!EMP_EmployeeDirectory.fields.dept_name,
recordType!EMP_EmployeeDirectory.fields.manager_name
},
filters: a!queryFilter(
field: recordType!EMP_EmployeeDirectory.fields.status,
operator: "=",
value: "ACTIVE"
),
pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 50)
).data


Common view mistakes

  1. Pointing Write Records at the view. Saves go to the base tables (employee, department). The view is only for reading.
  2. 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.
  3. Using a plain JOIN for optional relationships. The LEFT JOIN on manager keeps employees who have no manager. An inner join would silently drop them from the grid.
  4. 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).
  5. 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:

  1. Confirm the invoice is still pending.
  2. Check that the approver’s limit covers the amount.
  3. Apply any active vendor discount.
  4. 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.

DELIMITER //

CREATE PROCEDURE sp_approve_invoice(
IN p_invoice_id INT,
IN p_approver VARCHAR(255),
IN p_comments VARCHAR(1000),
OUT p_approved_amt DECIMAL(12,2),
OUT p_result_code VARCHAR(30)
)
proc: BEGIN
DECLARE v_status VARCHAR(20);
DECLARE v_amount DECIMAL(12,2);
DECLARE v_vendor_id INT;
DECLARE v_approval_limit DECIMAL(12,2);
DECLARE v_discount_pct DECIMAL(5,2);

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET p_approved_amt = NULL;
SET p_result_code = 'DB_ERROR';
END;

START TRANSACTION;

-- Lock the row so two approvers can't approve the same invoice at once
SELECT status, gross_amount, vendor_id
INTO v_status, v_amount, v_vendor_id
FROM invoices
WHERE invoice_id = p_invoice_id
FOR UPDATE;

IF v_status IS NULL THEN
ROLLBACK;
SET p_result_code = 'NOT_FOUND';
LEAVE proc;
END IF;

IF v_status <> 'PENDING' THEN
ROLLBACK;
SET p_result_code = 'NOT_PENDING';
LEAVE proc;
END IF;

-- Approver must have authority for this amount
SELECT approval_limit
INTO v_approval_limit
FROM approver_limits
WHERE approver = p_approver;

IF v_approval_limit IS NULL OR v_amount > v_approval_limit THEN
ROLLBACK;
SET p_result_code = 'LIMIT_EXCEEDED';
LEAVE proc;
END IF;

-- Apply the vendor's early-payment discount if one is active today
SELECT COALESCE(MAX(discount_pct), 0)
INTO v_discount_pct
FROM vendor_discounts
WHERE vendor_id = v_vendor_id
AND CURDATE() BETWEEN valid_from AND valid_to;

SET p_approved_amt = ROUND(v_amount * (1 - v_discount_pct / 100), 2);

UPDATE invoices
SET status = 'APPROVED',
approved_amount = p_approved_amt,
approved_by = p_approver,
approved_on = NOW()
WHERE invoice_id = p_invoice_id;

INSERT INTO invoice_approval_history
(invoice_id, action, action_by, action_on, amount, comments)
VALUES
(p_invoice_id, 'APPROVED', p_approver, NOW(), p_approved_amt, p_comments);

COMMIT;
SET p_result_code = 'SUCCESS';
END proc //

DELIMITER ;


Calling it from a process model

Use the Execute Stored Procedure smart service:

  1. Add custom inputs named p_invoice_id, p_approver and p_comments. The name of each custom input must match the name of a corresponding stored procedure parameter. appian
  2. Map the OUT values in custom outputs. Parameters can be accessed using the ac!parameters activity class parameter, so save ac!parameters.p_result_code to pv!resultCode and ac!parameters.p_approved_amt to pv!approvedAmount. appian
  3. Add an XOR gateway on pv!resultCode:
  4. SUCCESS goes to a Sync Records node for the invoice, then to notifications.
  5. NOT_PENDING and LIMIT_EXCEEDED go back to the user with a clear message.
  6. DB_ERROR goes 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.


DELIMITER //

CREATE PROCEDURE sp_vendor_spend_summary(
IN p_from DATE,
IN p_to DATE
)
BEGIN
-- Totals are computed in separate subqueries. Joining invoices and
-- credit_notes directly would multiply rows and double-count amounts.
SELECT v.vendor_id,
v.vendor_name,
COALESCE(inv.invoice_count, 0) AS invoice_count,
COALESCE(inv.approved_total, 0) AS approved_total,
COALESCE(cr.credit_total, 0) AS credit_total,
COALESCE(inv.approved_total, 0)
- COALESCE(cr.credit_total, 0) AS net_spend
FROM vendors v
LEFT JOIN (
SELECT vendor_id,
COUNT(*) AS invoice_count,
SUM(approved_amount) AS approved_total
FROM invoices
WHERE status = 'APPROVED'
AND approved_on >= p_from
AND approved_on < p_to + INTERVAL 1 DAY
GROUP BY vendor_id
) inv ON inv.vendor_id = v.vendor_id
LEFT JOIN (
SELECT vendor_id,
SUM(credit_amount) AS credit_total
FROM credit_notes
WHERE issued_on BETWEEN p_from AND p_to
GROUP BY vendor_id
) cr ON cr.vendor_id = v.vendor_id
WHERE inv.vendor_id IS NOT NULL
OR cr.vendor_id IS NOT NULL
ORDER BY net_spend DESC;
END //

DELIMITER ;


Calling it from an interface:

a!localVariables(
local!spend: a!executeStoredProcedureForQuery(
dataSource: cons!AP_DATA_SOURCE,
procedureName: "sp_vendor_spend_summary",
inputs: {
a!storedProcedureInput(name: "p_from", value: ri!fromDate),
a!storedProcedureInput(name: "p_to", value: ri!toDate)
}
),
if(
local!spend.success,
a!gridField(
data: index(local!spend.results, 1, {}),
columns: {
a!gridColumn(label: "Vendor", value: fv!row.vendor_name),
a!gridColumn(label: "Invoices", value: fv!row.invoice_count),
a!gridColumn(label: "Approved", value: fv!row.approved_total),
a!gridColumn(label: "Credits", value: fv!row.credit_total),
a!gridColumn(label: "Net spend", value: fv!row.net_spend)
}
),
a!richTextDisplayField(
value: "Could not load vendor spend: " & local!spend.error
)
)
)

Limits to know before you rely on this function:

  1. 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
  2. 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 success before building the grid. appian
  3. 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
  4. The default timeout is 30 seconds. appian
  5. 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

  1. 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.
  2. To save data to one table, use Write Records.
  3. 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.
  4. 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:

  1. Execute Stored Procedure Smart Service (Appian docs)
  2. a!executeStoredProcedureForQuery() (Appian docs)
  3. a!executeStoredProcedureForQuery() limits (Appian docs)
JuniorDatabase