CREATE/ALTER DROP PROCEDURE

Stored procedures are a powerful feature of the BBj SQL engine that allows developers to embed portions of their application logic in the database. Putting logic in the database makes that functionality available to not only BBj programs, but also third party ODBC or JDBC applications via the SQL CALL statement.

Developers can use the BBj Enterprise Manager to create and modify stored procedures using a handy graphical interface. However, BBj also provides the CREATE PROCEDURE statement to manage stored procedures programmatically via SQL.

CREATE PROCEDURE Syntax

CREATEPROCEDUREproc_name[(parameters)]
['/path/to/source.prc''/path/to/config.bbx'][return_type][code_block]

In BBj 9.0 and higher, use the following syntax to include a description. When specifying a description, ALL options must be included as well.

CREATEPROCEDUREproc_name[(parameters)]
['/path/to/source.prc''/path/to/config.bbx'][return_type][code_block][description]

Parameter	Description
proc_name	Case insensitive name of the stored procedure. The name can be up to 32 characters in length and may not contain any spaces.
parameters	Optional list of parameters (no limit) to be passed in by the calling program. Parameters are separated by commas (,). Parameters are in the following format: PARAM_NAME sql_type_name [size] IN \| OUT \| IN_OUT The SQL type name should be a valid SQL data type such as CHAR, VARCHAR, INTEGER, etc. Size is optional depending on the data type (follow the same rules as CREATE TABLE). IN, OUT, or IN_OUT is the direction the parameter is usable (input, output, or both).
path to source	Path to the source code for the procedure. This value can include database globals such as DATA, DICTIONARY, etc.
path to config.bbx	Path to an alternate config.bbx file to use by the interpreter used for running the procedure.
return type	The type of information returned by the procedure. If it returns a result set, use RESULT_SET. Otherwise, use a valid SQL type in the format used on a call to CREATE TABLE (i.e. VARCHAR(10), etc.).
code block	Block of BBj code that will be executed when the stored procedure is called. The block must begin with {_BEGIN_} and end with {_END_}.
description	Provide a description for the stored procedure. This option is mandatory and only valid when all options are working.

Example

The following example creates a stored procedure in the ChileCompany demo database that accepts a Product Category as an input parameter and returns a result set containing the list of items in the ITEM file that match that category. The stored procedure code uses typical BBj READ RECORD calls and string templates to return the results:

CREATE PROCEDURE get_items (prod_cat CHAR(2) IN) RESULT_SET
{_BEGIN_}
REM Open the file
chan = UNT
OPEN (chan) "C:\Program Files\basis700\demos\chiledd\data\ITEM"

REM Get the parameter value specified by the calling program
sp! = BBJAPI().getFileSystem().getStoredProcedureData()
prod_cat$ = sp!.getParameter("PROD_CAT")

REM Create a memory record set to hold the results of
REM our read operations.
rs! = BBJAPI().createMemoryRecordSet("ITEM_NUM:C(6)")

REM Iterate over the file and find the items that
REM have the specified PROD_CAT
DIM rec$:"ITEM_NUM:C(6),DESC:C(30),PROD_CAT:C(2),STOCK_UOM:C(3),COST:N(12),WEIGHT:N(12),WT_UNIT:C(2),PRICE:N(12)"
while (1)
READ RECORD (chan, ERR=eof) rec$
if (rec.prod_cat$ = prod_cat$) then
REM Found a match, so add it to the Record Set
data! = rs!.getEmptyRecordData()
data!.setFieldValue("ITEM_NUM", rec.item_num$)
rs!.insert(data!)
endif
wend

eof:
CLOSE (chan)

REM Set the returned result set value to the record set.
sp!.setRecordSet(rs!)
{_END_}

ALTER PROCEDURE Syntax

ALTER PROCEDUREproc_name [(parameters)]
['/path/to/source.prc' '/path/to/config.bbx'][return_type][code_block]description

See the syntax for CREATE PROCEDURE above for information about each parameter.

Note: The ALTER PROCEDURE syntax requires that the statement specify a description.

Example

The following example modifies the procedure in the example from CREATE PROCEDURE to accept an additional parameter:

ALTER PROCEDURE get_items
(prod_cat CHAR(2) IN, max_price NUMERIC(10,2) IN) 'The description for the SPROC'

DROP PROCEDURE Syntax

DROP PROCEDURE proc_name [DELETE]

Drops the stored procedure from the database. Specify the optional DELETE qualifier to delete the source code file associated with the SPROC.

Example 1

The following example drops the procedure but leaves the SPROC .prc source code file in tact:

DROP PROCEDURE my_sproc

Example 2

The following example drops the procedure and also deletes the SPROC .prc source code file :

DROP PROCEDURE my_sproc DELETE