Sybase Adaptive Server Anywhere - Page 429

Chapter 9 SQL Statements
411
♦ Call the
sp_customer_list procedure. This procedure has no parameters,
and returns a result set.
CALL sp_customer_list()
♦ The following Interactive SQL example creates a procedure to return the
number of orders placed by the customer whose ID is supplied, creates a
variable to hold the result, calls the procedure, and displays the result.
CREATE PROCEDURE OrderCount (IN customer_ID INT, OUT
Orders INT)
BEGIN
SELECT COUNT("DBA".sales_order.id)
INTO Orders
FROM "DBA".customer
KEY LEFT OUTER JOIN "DBA".sales_order
WHERE "DBA".customer.id = customer_ID;
END
go
-- Create a variable to hold the result
CREATE VARIABLE Orders INT
go
-- Call the procedure, FOR customer 101
CALL OrderCount ( 101, Orders)
go
-- Display the result
SELECT Orders FROM DUMMY
go
Examples