dbo.fSQLProcedureColumns

dbo.fSQLProcedureColumns

This function returns the list of input and output parameters and the columns that make up the result set for the specified stored procedure or user-defined function. See also CREATE PROCEDURE and CREATE FUNCTION.

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

Table 118

dbo.fSQLProcedureColumns 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.

procedure_column_name

(no default value)

Name of the column in the procedure.

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

Returned Result Set

Table 119

dbo.fProcedureColumns 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

SMALLINT not NULL

Defines the procedure column as a parameter or a result set column:

•

0 = SQL_PARAM_TYPE_UNKNOWN—procedure column is a parameter whose type is unknown.

•

1 = SQL_PARAM_INPUT—procedure column is an input parameter.

•

2 = SQL_PARAM_INPUT_OUTPUT—procedure column is an input/output parameter.

•

3 = SQL_RESULT_COL—procedure column is a result set column.

•

4 = SQL_PARAM_OUTPUT—procedure column is an output parameter.

•

5 = SQL_RETURN_VALUE—procedure column is the return value of the procedure.

SMALLINT not NULL

ODBC SQL data type. See also Pervasive PSQL Supported Data Types.

VARCHAR not NULL

Relational data type name. See also Pervasive PSQL Supported Data Types.

Size of the procedure column in the database. NULL is returned for data types where column size is not applicable. See also Precision and Scale of Decimal Data Types.

Length in bytes of data transferred on an SQLGetData or SQLFetch operation if SQL_C_DEFAULT is specified. For numeric data, this size may be different than the size of the data stored in the database. See also Pervasive PSQL Supported Data Types.

Number of decimal digits of the procedure column in the database. NULL is returned for data types where decimal digits is not applicable. See also Precision and Scale of Decimal Data Types.

For numeric data types, either 10 or 2.

•

10—the values in COLUMN_SIZE and DECIMAL_DIGITS give the number of decimal digits allowed for the column.

•

2—the values in COLUMN_SIZE and DECIMAL_DIGITS give the number of bits allowed in the column.

NULL is returned for data types where RADIX is not applicable.

SMALLINT not NULL

Indicates whether the procedure column accepts a NULL value:

•

0 = SQL_NO_NULLS—the procedure column does not accept NULL values.

•

1 = SQL_NULLABLE—the procedure column accepts NULL values.

•

2 = SQL_NULLABLE_UNKNOWN—it is not known if the procedure column accepts NULL values.

The description of the procedure column

The default value of the column.

If NULL was specified as the default value, this column is the word NULL, not enclosed in quotation marks. If the default value cannot be represented without truncation, this column contains TRUNCATED, with no enclosing single quotation marks. If no default value was specified, this column is NULL.

SMALLINT not NULL

Value of the SQL data type as it appears in the SQL_DESC_TYPE field of the descriptor. This column is the same as the TYPE_NAME column, except for data types DATE, TIME, DATETIME, and TIMESTAMP.

For those data types, the SQL_DATA_TYPE field in the result set returns the following SQL_DATE for DATE, SQL_TIME for TIME, and SQL_TIMESTAMP for DATETIME and TIMESTAMP.

SQL_DATETIME_SUB

Subtype code for DATE, TIME, DATETIME, and TIMESTAMP. For other data types, this column returns a NULL.

1 for DATE (SQL_CODE_DATE)

2 = for TIME (SQL_CODE_TIME)

3 = for DATETIME and TIMESTAMP (SQL_CODE_TIMESTAMP)

CHAR_OCTET_LENGTH

Maximum length in bytes of a character or binary data type column. For all other data types, this column returns a NULL.

ORDINAL_POSITION

INTEGER not NULL

For input and output parameters, the ordinal position of the parameter in the procedure definition (in increasing parameter order, starting at 1).

For a return value (if any), 0 is returned. For result-set columns, the ordinal position of the column in the result set, with the first column in the result set being number 1.

"NO" if the column does not include NULLs.

"YES" if the column includes NULLs.

This column returns a zero-length string if nullability is unknown. The value returned for this column differs from the value returned for the NULLABLE column.

By default, the DEMODATA sample database does not contain any stored procedures or user-defined functions. To provide output for the dbo.fSQLProcedureColumns function, you can create the stored procedures and user-defined function provided in the example for dbo.fSQLProcedures. This example assumes that DEMODATA contains the stored procedures “curs1,” “Enrollstudent2,” “AInsert,” and “AR,” and the user-defined function “CalInterest.”

The following statement returns information for all columns in all stored procedures and user-defined functions in the DEMODATA sample database:

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

Result Set (abbreviated for space considerations):

PROCEDURE_NAME COLUMN_NAME COLUMN_TYPE DATA_TYPE

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

AInsert :AGUID 1 -2

AInsert :APeriod 1 4

AInsert :BBal 1 -6

AInsert :BCr 1 3

AInsert :BDr 1 3

AInsert :BNet 1 3

AInsert :HTrx 1 -6

AInsert :PCr 1 3

AInsert :PDr 1 3

AInsert :PNet 1 3

CalInterest :period 1 7

CalInterest :principle 1 8

CalInterest :rate 1 8

CalInterest :RETURN_VALUE 5 8

curs1 :Arg1 1 1

curs1 :Arg2 1 4

Enrollstudent2 :Class_Id 1 4

Enrollstudent2 :Stud_id 1 4

20 rows were affected.