If your database reference is a stored procedure then you must execute the stored procedure to obtain any results. You may not use any of the record selection techniques that you use for tables. You execute the procedure by making one of the following assignment statements:

$set_var DB_SELECT "EXEC=parmlist"

$set_var DB_SELECT "EXEC2=parmlist"

Use the first command to execute a procedure that returns a record set. Use the second command to execute a procedure that does not return a record set. Either command will return result parameters.

Use parmlist to pass any procedure parameters you wish to set. Each parameter in the list is separated from the others by spaces or commas. You identify parameters by name or number. If you use parameter names, do not include the leading @ sign because this is the macro expansion character for CopiaFacts. For example, if your first parameter is @username then you could identify it in either of the two statements below:

$set_var DB_SELECT "EXEC=username=Joe"

$set_var DB_SELECT "EXEC=1=Joe"

If your parameter value contains spaces or commas then you must enclose it in single quotes as shown below:

$set_var DB_SELECT "EXEC='username=Joe Smith'"

If your parameter value contains spaces or commas and a single quote as well, then you must use two single quotes to represent one single quote as shown below:

$set_var DB_SELECT "EXEC='username=Joe O''Brien'"

Multiple parameters are separated by one or more spaces and should be enclosed in single quotes as necessary. It never hurts to enclose a parameter in single quotes. An example of two parameters is shown below:

$set_var DB_SELECT "EXEC='1=John Smith' 2=HQ"

Stored procedures may return record sets. If so, the return value will be a positive number indicating the number of records in the result. Stored procedures may also return parameter values. These values are not available until the record set is closed. The return values will be placed in variables named using the prefix ID followed by the parameter name. The actual procedure return code will (for SQL server) be placed in a variable named PFX%RETURN_VALUE where PFX% is the ID prefix and RETURN_VALUE is the standard parameter name defined in the database software for the stored procedure return value. You should check documentation for your database to see what name is assigned to the return value parameter.

If a stored procedure returns parameter results, then these will not be available until you close the database if the procedure also returns a record set. If no records are returned, then the parameter results will be available upon completion of the procedure.