Stored procedures are created based upon an existing command file. Once you have connected to your database, a new stored procedure can be created. Options for initially creating a new stored procedure include selecting the options "Stored Procedures" > "New Stored Procedure..." from the Group Bar within the Database Explorer, or by using the PUT command.
When creating a stored procedure from the Database Explorer, the following dialog will be presented in order to enter the stored procedure name, an existing command file, the arguments, the return value data type and size, and a comment.
Arguments
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.
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.
After creating your stored procedure, the name, comment, size, latest revision date, and version number will be displayed in the Database Explorer.
