SQL CALL

What is the SQL CALL statement?

Executes a stored procedure or function on the database server.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CALL: MySQL, MariaDB, PostgreSQL (functions via SELECT), SQL Server, Oracle, IBM Db2, Snowflake, BigQuery (CALL supported), Firebird.

SQL CALL Full Explanation

The SQL CALL statement runs a stored procedure (or, in some dialects, a user-defined function) within the database engine. Procedures encapsulate logic such as data manipulation, calculations, or control-of-flow code. CALL sends control to the procedure, passes any IN parameters, and optionally receives OUT or INOUT parameters and result sets. Because procedures run on the server, they reduce network round-trips, centralize business logic, and allow privileges to be managed in one place. CALL is part of the SQL:1999 standard and is implemented in most enterprise databases, though syntax details (parameter markers, assignment of OUT parameters, multiple result sets) differ by vendor. In interactive sessions it can appear alone; in application code it is typically issued through a client driver that supports parameter binding. When the procedure finishes, execution returns to the caller with any output values set. CALL cannot be used to invoke plain SQL scripts or anonymous blocks; it is strictly for stored objects created with CREATE PROCEDURE (or CREATE FUNCTION where supported).

SQL CALL Syntax

CALL procedure_name();
CALL procedure_name(argument1, argument2, ...);

-- Assign OUT/INOUT parameters (MySQL example)
CALL procedure_name(IN param1, OUT param2);

-- PostgreSQL uses SELECT for functions
SELECT * FROM procedure_name(argument1, argument2);

SQL CALL Parameters

procedure_name STRING - The exact name (optionally schema-qualified) of the stored procedure to invoke.
argumentN ANY - One value per formal parameter, passed by position unless the dialect supports named parameters.

Example Queries Using SQL CALL

-- 1. Run a procedure with no parameters
CALL refresh_materialized_views();

-- 2. Pass two IN parameters
CALL add_order(1001, CURRENT_DATE);

-- 3. Capture OUT parameters (MySQL)
CALL get_user_stats(42, @posts, @followers);
SELECT @posts AS posts, @followers AS followers;

-- 4. Multiple result sets (SQL Server / MariaDB)
CALL sales_by_region('2024-01-01', '2024-03-31');

Expected Output Using SQL CALL

The database executes the specified procedure
Side effects such as inserts, updates, or logging occur inside the procedure
Any result sets are streamed back to the client
OUT/INOUT parameters are populated and can be fetched with SELECT or driver-specific APIs

Use Cases with SQL CALL

Encapsulating complex business logic and reusing it across applications.
Performing batch updates close to the data to minimize latency.
Returning multiple result sets from one invocation.
Enforcing security by granting EXECUTE on a procedure instead of direct table access.

Common Mistakes with SQL CALL

Forgetting to create the procedure before calling it.
Mismatching the number or order of parameters.
Using CALL for a function in a dialect that requires SELECT.
Attempting to capture OUT parameters without session variables or proper bindings.
Assuming every dialect supports multiple result sets via CALL.

First Introduced In

SQL:1999

Frequently Asked Questions

What is the difference between CALL and EXEC?

Many databases treat CALL and EXEC/EXECUTE as synonyms, but some (SQL Server) prefer EXEC while others (MySQL, Oracle) standardize on CALL. Always follow your dialect’s convention.

How do I pass NULL values to a stored procedure?

Simply include NULL in the argument list: `CALL update_salary(emp_id => 101, new_salary => NULL);` The procedure must accept NULL or handle it internally.

Can I nest CALL statements inside another procedure?

Yes. A stored procedure can invoke other procedures via CALL, enabling modular design. Ensure you manage transactions and error handling to avoid unintended rollbacks.

Does CALL commit or rollback transactions automatically?

Behavior is dialect-specific. MySQL commits implicitly if autocommit is on. PostgreSQL and Oracle leave transaction control to the caller. Always check your database’s transaction model.