| Home | Previous Lesson: Stored Procedures Next Lesson: SQL Server Stored Procedures |
A simple example of a procedure is shown below:
CREATE PROCEDURE sp_list_item_product_balance
(IN pProduct_balance integer)
RESULT(product_no integer, product_description char(32),
product_balance integer)
BEGIN
SELECT product_no, product_description, product_balance
FROM product_master
WHERE product_balance > pProduct_balance
END ;
Note that we place any required parameters into the brackets following the procedure name. The keyword IN specifies the purpose of the parameter, and can be either IN, OUT or INOUT, and is followed by the name of the parameter and its data type.
In the result parentheses, you need to declare all the columns returned by the SELECT statement. We then declare the SELECT statement between the BEGIN and END keywords.
The names in the result declaration can be same as the column names.
We could execute this procedure from the Database Administration Painter, using the following syntax:
EXECUTE sp_list_item_product_balance( 1000 );
There are many limitations to SQL Anywhere stored procedures, such as, without conditional statements, you can't have more than one SQL statement and you can't specify a default value to be used as the parameter.
| Home | Previous Lesson: Stored Procedures Next Lesson: SQL Server Stored Procedures |