dbo.fSQLProcedures

dbo.fSQLProcedures

This function returns as a result set the names of stored procedures and user-defined functions in the current database or the specified database. See also CREATE PROCEDURE and CREATE FUNCTION.

dbo.fSQLProcedures (<'database_qualifier' | null>, <'procedure_name' | null>)

Table 116

dbo.fSQLProcedures Arguments

database_qualifier

Current database

Name of the database from which the details are to be obtained.

(no default value)

Name of the stored procedure whose information is required.

Note: System stored procedures are defined in the internal PERVASIVESYSDB database, which does not display in Pervasive PSQL Control Center.

Returned Result Set

Table 117

dbo.fProcedures Returned Result Set

PROCEDURE_QUALIFIER

Name of the database in which the procedure was created; NULL if not applicable to the database.

PROCEDURE_OWNER

Procedure schema identifier; NULL if not applicable to the database.

VARCHAR not NULL

Procedure identifier

NUM_INPUT_PARAMS

Reserved for future use. Do not use for your application.

NUM_OUTPUT_PARAMS

Reserved for future use. Do not use for your application.

NUM_RESULT_SETS

Reserved for future use. Do not use for your application.

The description of the procedure

Defines the procedure type:

•

0 = SQL_PT_UNKNOWN—it cannot be determined whether the procedure returns a value.

•

1 = SQL_PT_PROCEDURE—the returned object is a procedure and does not have a return value.

•

2 = SQL_PT_FUNCTION—the returned object is a function and has a return value.

By default, the DEMODATA sample database does not contain any stored procedures or user-defined functions. To provide output for the dbo.fSQLProcedures function (and for dbo.fSQLProcedureColumns), you can create the following stored procedures and user-defined function. They can all be CALLed provided the required tables and parameter bindings are present. However, the point for this example is to include them as database objects rather than demonstrate their execution.

See also CREATE PROCEDURE and CREATE FUNCTION.

CREATE PROCEDURE curs1 (IN :Arg1 CHAR(4), IN :Arg2 INTEGER) AS BEGIN

DECLARE :alpha char(10) DEFAULT 'BA';

DECLARE :beta INTEGER DEFAULT 100;

DECLARE degdel CURSOR FOR

SELECT degree, cost_per_credit FROM tuition WHERE Degree = :Arg1 AND cost_per_credit = :arg2

FETCH NEXT FROM degdel INTO :alpha, :beta

DELETE WHERE CURRENT OF degdel;

CREATE PROCEDURE EnrollStudent2 (IN :Stud_id INTEGER, IN

:Class_Id INTEGER);

INSERT INTO Enrolls VALUES (:Stud_id, :Class_Id, 0.0);

CREATE PROCEDURE AInsert

(IN :AGUID BINARY(16),

IN :APeriod INT,

IN :BBal UTINYINT,

IN :BDr DECIMAL(23,9),

IN :BCr DECIMAL(23,9),

IN :BNet DECIMAL(23,9),

IN :HTrx UTINYINT,

IN :PDr DECIMAL(23,9),

IN :PCr DECIMAL(23,9),

IN :PNet DECIMAL(23,9))

INSERT INTO "ASum" ("AID", "APeriod", "IBal", "BDr", "BCr", "BNet", "HTrx", "PDr", "PCr", "PNet") VALUES (:AGUID,:APeriod,:BBal,:BDr,:BCr,:BNet,:HTrx, :PDr,:PCr,:PNet);

CREATE PROCEDURE AR (OUT :BIID SMALLINT, IN :BName CHAR(30))

SELECT MAX(BID) + 1 INTO :BIID FROM Br;

INSERT INTO Br (BID, FName) VALUES (:BIID, :BName);

CREATE FUNCTION CalInterest (IN :principle FLOAT,

IN :period REAL, IN :rate DOUBLE)

DECLARE :interest DOUBLE;

SET :interest = ((:principle * :period * :rate) /

RETURN (:interest);

SELECT * FROM dbo.fSQLProcedures ('Demodata', null)

Result Set (abbreviated for space considerations):

PROCEDURE_QUALIFIER PROCEDURE_NAME PROCEDURE_TYPE

=================== ================= ==============

Demodata curs1 1

Demodata Enrollstudent2 1

Demodata AInsert 1

Demodata CalInterest 2

5 rows were affected.