Calling a Stored Procedure

You can call a stored procedure using the SQL CALL command.

In order for a user to call a stored procedure, the user’s role must have the USAGE privilege for the stored procedure.

Once you have the privileges to call the stored procedure, you can use a CALL statement to call the stored procedure.

Note

To both create and call an anonymous procedure, use CALL (with Anonymous Procedure). Creating and calling an anonymous procedure does not require a role with CREATE PROCEDURE schema privileges.

If the stored procedure has arguments, you can specify those arguments by name or by position.

For example, the following stored procedure accepts three arguments:

CREATE OR REPLACE PROCEDURE sp_concatenate_strings(
    first VARCHAR,
    second VARCHAR,
    third VARCHAR)
  RETURNS VARCHAR
  LANGUAGE SQL
  AS
  $$
  BEGIN
    RETURN first || second || third;
  END;
  $$;
Copy

When calling the procedure, you can specify the arguments by name:

CALL sp_concatenate_strings(
  first => 'one',
  second => 'two',
  third => 'three');
Copy

If you specify the arguments by name, you do not need to specify the arguments in any particular order:

CALL sp_concatenate_strings(
  third => 'three',
  first => 'one',
  second => 'two');
Copy

You can also specify the arguments by position:

CALL sp_concatenate_strings(
  'one',
  'two',
  'three');
Copy

Note the following:

  • You must either specify all arguments by name or by position. You cannot specify some of the arguments by name and other arguments by position.

    When specifying an argument by name, you cannot use double quotes around the argument name.

  • If two functions or two procedures have the same name but different argument types, you can use the argument names to specify which function or procedure to execute, if the argument names are different. Refer to Overloading Procedures and Functions.

Examples

To execute a stored procedure, use a CALL statement. For example:

call stproc1(5.14::FLOAT);
Copy

Each argument to a stored procedure can be a general expression:

CALL stproc1(2 * 5.14::FLOAT);
Copy

An argument can be a subquery:

CALL stproc1(SELECT COUNT(*) FROM stproc_test_table1);
Copy

You can call only one stored procedure per CALL statement. For example, the following statement fails:

call proc1(1), proc2(2);                          -- Not allowed
Copy

Also, you cannot use a stored procedure CALL as part of an expression. For example, all the following statements fail:

call proc1(1) + proc1(2);                         -- Not allowed
call proc1(1) + 1;                                -- Not allowed
call proc1(proc2(x));                             -- Not allowed
select * from (call proc1(1));                    -- Not allowed
Copy

However, inside a stored procedure, the stored procedure can call another stored procedure, or call itself recursively.

Caution

Nested calls can exceed the maximum allowed stack depth, so be careful when nesting calls, especially when using recursion.

Language: English