ODBC Server Reference Manual

Stored Procedures

HP NonStop ODBC Server Reference Manual—429151-002

5-4

Execution of Stored Procedures

Stored procedures can be invoked through the NonStop ODBC Server using either the

CORE SQL syntax or the Transact-SQL syntax. The following conditions must be met

for successful execution of a stored procedure:

•

The stored procedure must have been created as a Pathway server (see

Development of Stored Procedures on page 5-6).

•

Information regarding the stored procedure must exist in the NonStop ODBC

Server catalog tables

The stored procedure can have input and output parameters and a return status code.

It can also return “result sets,” which are the output of SQL SELECT statements.

The following subsections describe stored procedure invocation from both CORE SQL

and Transact-SQL.

Invoking Stored Procedures in CORE SQL

Stored procedure execution is an extension of ODBC that is supported by the NonStop

ODBC Server. CORE SQL uses the CALL statement to invoke stored procedures. The

following describes the syntax of the statement. The CALL statement, and the ODBC

“escape clause” format that enables the use of extensions to CORE SQL, are

described in greater detail in Section 3, CORE SQL Language.

Execution of the stored procedure results in the following:

•

If the stored procedure contains SELECT statements, the caller receives a result

set for every SELECT statement. A result set consists of a description of the

columns in the result rows together with zero or more rows of values. UPDATE,

INSERT, and DELETE statements return a count of affected rows.

In the following example, the procedure GET_EMPNAME is called to obtain the

names of employees whose IDs are between 1000 and 1010. The result set

consists of columns LAST_NAME and FIRST_NAME, and three rows of data.

{ ? = CALL GET_EMPNAME (1000,1010) }

--*( VENDOR(Microsoft),PRODUCT(ODBC)

[ ?= ] CALL procedure-name [ ( parameter [, ... ] ) ] )*--

{ [?=] CALL procedure-name [ ( parameter [, ... ] ) ] }

Note. In the escape clause format, the --*( ... )*-- or { ... } enclosure is a required element.