Creates or replaces stored procedures into the database.
Options
argname datatype
The parameter name and data type. This portion may be repeated.
comment
An optional comment for the parameter or, if placed after RETURN, an optional comment for the entire procedure. The comment must be enclosed in the current quote setting.
filename
The filename in ASCII text format, with full path, to load as the stored procedure.
procname
Specifies the procedure name. If a procedure by this name already exists in the database, an error is generated. The procedure name is limited to 128 characters.
RETURN datatype
Determines the data type of the value returned by the procedure.
About the PUT Command
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 &p1 SET col = 99 WHERE col = .p2
The arguments names are specified when the procedure is stored in the database with the PUT command.
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.
Replacing a Procedure
If you are replacing an existing procedure, you must LOCK the procedure first either with 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.
Example
Use the PUT command as follows to store a command file as a stored procedure:
PUT INS.RMD AS SP_ContCheck p1 INT, p2 TEXT RETURN INTEGER
The contents of INS.RMD could be something like:
--INS.RMD
IF (.p1 > 105) THEN
INSERT INTO contact (custid, contlname) VALUES (.p1, .p2)
RETURN 1
ELSE
RETURN 0
ENDIF
See Also:
