ALLBASE/SQL Reference Manual (36216-90216)

344 Chapter10
SQL Statements A - D
CREATE PROCEDURE
• Indicator variables are not allowed or needed inside procedures. However, you can
include an indicator variable with a host variable in supplying a value to a parameter in
EXECUTE PROCEDURE, DECLARE CURSOR, OPEN, or CLOSE statements.
Indicator variables specified for output host variables in CLOSE, DECLARE CURSOR, or
EXECUTE PROCEDURE statements are set by ALLBASE/SQL.
• Syntactic errors are returned along with an indication of the location of the error inside
the CREATE PROCEDURE statement.
• Statements that support dynamic processing are not allowed within a procedure.
• Within a procedure, a single row SELECT statement (one having an INTO clause) that
returns multiple rows will assign the first row to output host variables or procedure
parameters, and a warning is issued. In an application, this case would generate an
error.
• If the IN
DBEFileSetName clause is specified, but the procedure owner does not have
SECTIONSPACE authority for the specified DBEFileSet, a warning is issued and the
default SECTIONSPACE DBEFileSet is used instead.
Authorization
You must have RESOURCE or DBA authority to create a procedure. If you do not have all
appropriate authorities on the objects referenced by the procedure when you create the
procedure, warnings are returned. If you do not have the appropriate authorities at
execution time, errors are returned but (except in a rule) the execution of the rest of the
procedure does not stop. The procedure owner becomes the owner of any object created by
the procedure with no owner explicitly specified. A user granted authority to execute a
procedure need not have any direct authority on the objects accessed by the procedure.
To specify a
DBEFileSetName , the procedure owner must have SECTIONSPACE authority
on the referenced DBEFileSet.
Examples
1. DELETE
CREATE PROCEDURE ManufDB.RemoveBatchStamp (BatchStamp DATETIME NOT NULL)
AS
BEGIN
DELETE FROM ManufDB.TestData WHERE BatchStamp = :BatchStamp;
IF ::sqlcode < > 0 THEN
PRINT 'Delete failed.';
ENDIF;
END;