Argument List
When you load a stored procedure into a database, you specify arguments to be passed to it. These arguments are used within the procedure. When the procedure is called, the number and type of arguments passed must match the number and type specified when the procedure was stored in the database. When an argument name is referenced in the stored procedure code, the argument name must be preceded by a period unless it is a table or column name, then it must be preceded by an ampersand (&). For example:
UPDATE &ProcedureTable SET ColumnName = 100 WHERE ColumnName = .vValue
In addition to containing a table name or column name the ampersand variable would be used, if the variable is part of a command, is located on the left side of an operator, contains an ORDER BY clause, or contains a WHERE clause.
The arguments names are specified when the procedure is stored in the database.
Return Values
The value to be returned by a stored procedure is specified in the procedure code following the keyword RETURN. For example, RETURN 'Los Angeles'.
The value returned must match the data type specified when the procedure was stored.
Notes:
•If you are replacing an existing procedure, you must LOCK the procedure first with either the GET LOCK or the SET PROCEDURE command. Once the procedure is locked, it is replaced by an updated file using the PUT command. A procedure cannot be replaced unless it is locked. A procedure is automatically unlocked when replaced with the PUT command.
•The RETURN varname option is used ONLY within a stored procedure to return a value. The returned value is stored in the STP_RETURN system variable. This option will return an -ERROR- when used outside a stored procedure. The default is TEXT 8 characters, but if you want more, you can set it to a larger value. You can control the maximum length at procedure definition time, or by editing the SYS_PROC_LEN column in SYS_PROC_COLS system table.
To set the limit for the RETURN value to 30 characters:
PUT MyTest.PRC AS MyTest P1 INTEGER RETURN TEXT (30)
•To clear any previous arguments that were stored for a procedure:
PUT filename AS procname RETURN
