call

Call a stored procedure

Syntax

call_stmt:
          CALL [database_name.]proc_name [(proc_arg[, proc_arg]...)]

Description

Stored procedures are executed via the SQL CALL statement as shown in the above syntax. The name of the procedure is specified by the proc_name identifier which can be optionally qualified by database_name, the name of the database with which the procedure is associated.

Input only (IN) arguments that are passed in to the procedure through the CALL statement must be of a compatible data type. The argument variables specified in the CALL statement for an OUT or an INOUT argument must be a var_name or bound parameter reference that matches its corresponding formal argument's data type.

Example

create proc authors_books(lastnm char) as
    select publ_yr, title from book where last_name = lastnm
end proc;
    ...
authors_books("PotterB");
PUBL_YR TITLE
   1903 The Tailor of Gloucester
   1903 The tale of Squirrel Nutkin
   1904 The tale of Benjamin Bunny
   1904 The tale of Peter Rabbit; thirty-one illustrations.
   1905 The pie and the patty-pan.
   1905 The tale of Mrs. Tiggy-Winkle
   1906 The tale of Mr. Jeremy Fisher
   1908 The tale of Jemima Puddle-Duck
   1907 The tale of Tom Kitten
   1911 The tale of Timmy Tiptoes
   1912 The tale of Mr. Tod
   1913 The tale of Pigling Bland
   1918 The tale of Johnny Town-mouse
    ...
create procedure sold(pid char, bid char, offer double, sale_date char) as
    start transaction
    update book set price = offer, date_sold = sale_date where bookid = bid
    insert into sale values bid, pid
    commit
end proc;
    ...
execute sold("SMD", "potter08", 750.0, date "2011-04-03");
    ...