For the full syntax of the CREATE PROCEDURE statement, see CREATE PROCEDURE.
Note Every declare statement must end with a semicolon (;).
The variable name is an alphanumeric string that identifies the variable. The data type of the variable can be any valid SQL data type that is supported. For supported data types, see SQL: Data types.
Local variables that are used inside the procedure (for temporary storage of column and control values) are defined in a separate section of the stored procedure that directly follows the BEGIN keyword.
For example:
"CREATE PROCEDURE phonebook_search (first_name VARCHAR, last_name VARCHAR) RETURNS (phone_nr NUMERIC, city VARCHAR) BEGIN DECLARE i INTEGER;
DECLARE dat DATE;
END";
Note that input and output parameters are treated like local variables within a procedure with the exception that input parameters have a preset value and output parameter values are returned or can be appended to the returned result set.