Release 10.1A: OpenEdge Development:
Progress 4GL Reference

> ## RUN STORED-PROCEDURE statement

> Runs a non-Progress stored procedure or allows you to send SQL to an SQL-based data source using an OpenEdge DataServer.

> #### Syntax

>     RUN STORED-PROCEDURE procedure
>       [ integer-field = PROC-HANDLE ]
>       [ NO-ERROR ]
>       [ ( parameter [ , parameter ] ... ) ]

> ``procedure``

> The name of the stored procedure that you want to run or the Progress built-in procedure name, `send-sql-statement`, to send SQL to an SQL-based data source.

> ``integer-field` = PROC-HANDLE`

> Assigns a value to the specified integer field or variable (`integer-field`) that uniquely identifies the stored procedure returning results from the non-OpenEdge database or that uniquely identifies the SQL cursor used to retrieve results from an SQL-based, ODBC-compliant data source.

> `NO-ERROR`

> Specifies that any ERROR conditions that the RUN STORED-PROCEDURE statement produces are suppressed. Before you close a stored procedure, check the ERROR-STATUS handle for information on any errors that occurred. You receive an error when you attempt to close a stored procedure that did not start.

> Note: This option must appear before any run-time parameter list.

> ``parameter``

> A run-time parameter to be passed to the stored procedure. A `parameter` has the following syntax:

>     [ INPUT | OUTPUT | INPUT-OUTPUT ]
>       [ PARAM parameter-name = ] expression

> An `expression` is a constant, field name, variable name, or expression. INPUT is the default. OUTPUT and INPUT-OUTPUT parameters must be record fields or program variables. For ORACLE, OUTPUT and INPUT-OUTPUT work the same way.

> If you run send-sql-statement for an SQL-based data source, you must pass a single character expression `parameter` containing the SQL statement you want the data source to execute.

> If you do not specify `parameter-name` (the name of a keyword parameter defined by the stored procedure), you must supply all of the parameters in correct order. If you do specify `parameter-name`, you must precede your assignment statement with the keyword PARAM. If you do not supply a required parameter, and no default is specified in the stored procedure, you receive a run-time error.

> Examples

> This procedure runs the ORACLE stored procedure pcust and writes the results of the stored procedure into the Progress-supplied buffer, proc-text-buffer. The same code works for accessing a stored procedure from an ODBC-compliant data source.

>     DEFINE VAR intvar AS INTEGER.

>     RUN STORED-PROCEDURE pcust intvar = PROC-HANDLE
>     (10, OUTPUT 0, OUTPUT 0) NO-ERROR.
>     FOR EACH proc-text-buffer WHERE PROC-HANDLE = intvar:
>       DISPLAY proc-text-buffer.
>     END.
>     IF ERROR-STATUS:ERROR
>     THEN DO:
>       MESSAGE "Stored Procedure failed to run".
>     END.
>     ELSE CLOSE STORED-PROCEDURE pcust WHERE PROC-HANDLE = intvar.

> This procedure uses the `send-sql-statement` option of the RUN STORED-PROCEDURE statement to send SQL to ORACLE. It writes the results of the stored procedure into the Progress-supplied buffer, proc-text-buffer. The same code works for sending SQL to an ODBC-compliant data source:

>     DEFINE VAR handle1 AS INTEGER.

>     RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE
>          ("SELECT name, cust_num FROM customer").
>     FOR EACH proc-text-buffer WHERE PROC-HANDLE = handle1:
>        display proc-text.
>        END.
>     CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1.
>     END.

> This code example shows how to trap errors from the non-OpenEdge RDBMS within a procedure:

>     DEFINE VAR h1 AS INTEGER.
>     DEFINE VAR j AS INTEGER.
>     RUN STORED-PROC send-sql-statement h1 = PROC-HANDLE NO-ERROR
>       ("select count (*) from xxx.customer where name between ’A’ and ’Z’ ").

>     IF ERROR STATUS:ERROR THEN DO:
>       DO j = 1 TO ERROR-STATUS:NUM-MESSAGES:
>         MESSAGE "error" ERROR-STATUS:GET-NUMBER(j)
>             ERROR-STATUS:GET-MESSAGE(j).
>       END.
>     END.

>     CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = h1.

> #### Notes

> - The RUN STORED-PROCEDURE statement starts a transaction with the same scope as transactions started with the UPDATE statement.

> - For more information on using this statement and on using the built-in procedure name, `send-sql-statement`, see the OpenEdge DataServer Guides, OpenEdge Data Management: DataServer for Microsoft SQL Server , OpenEdge Data Management: DataServer for ODBC , and OpenEdge Data Management: DataServer for ORACLE .

> #### See also

> CLOSE STORED-PROCEDURE statement, PROC-HANDLE function, PROC-STATUS function

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095