Control / script · All operations

Call a stored procedure

A variant of Run a script

Invoke a procedure stored in the database, optionally once per row, and return its results.

Status

In arcform

Not built yet

arcform does not do this yet; it would live in how a Protocol runs. Its place on the roadmap

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache Hop (1)
  • Call DB procedureread 2026-09-27

    Call a database stored procedure or function per row and return its results

Azure Data Factory (1)
  • Stored Procedureread 2026-09-27

    Invoke a stored procedure in Azure SQL, Azure Synapse Analytics or SQL Server

Pentaho Data Integration (1)
Talend (1)

Background

A stored procedure is a subroutine available to applications that access a relational database, stored in the database's data dictionary.[1] A stored procedure may return result sets, the results of a SELECT statement.[1]

When to use it

A stored procedure can run directly within the database engine, which saves network costs, most of all when a series of SQL statements is involved.[1] Stored procedures let programmers embed business logic in the database as an interface, which can reduce the need to encode the logic in client programs.[1] Call a procedure when the logic already lives in the database and is owned there; copying it into the pipeline leaves two versions to keep in step. (Meridian's view)

Stored procedure languages are often vendor-specific, and changing database vendors usually requires rewriting existing stored procedures.[1] Changes to stored procedures are harder to keep track of in a version control system than other code.[1]

In Apache Hop, each argument of Call DB procedure is IN (input only), OUT (output only) or INOUT, whose incoming value is sent and replaced with the returned value.[2] Azure Data Factory's Stored Procedure activity does not support output parameters, and a Lookup or Script activity is the way to run a procedure that returns them.[3]

The method

Depending on the database system, stored procedures can be implemented in languages such as SQL, Java, C or C++.[1] Stored-procedure flow-control statements typically include IF, WHILE, LOOP, REPEAT and CASE.[1]

In Apache Hop, a procedure that only returns a result set can also be called from Table Input as ordinary SQL.[2]

Compared with the alternatives

Stored procedures are similar to user-defined functions, but a user-defined function can be used like any other expression within SQL statements, whereas a stored procedure must be invoked using the CALL statement.[1] Like stored procedures, prepared statements are stored on the server for efficiency and give some protection from SQL injection attacks, but they are not ordinarily written to use procedural logic.[1]

Sources

  1. [1]Stored procedure - Wikipediasecondary source
  2. [2]Call DB procedure | Apache Hopdocumentation
  3. [3]Transform data by using the Stored Procedure activity - Azure Data Factory & Azure Synapse | Microsoft Learndocumentation