Release 10.1A: OpenEdge Data Management:
DataServer for Microsoft SQL Server

> ## Run Stored-Proc statement execution using the send-sql-statement option

> The Progress 4GL also allows you to use stored-procedure syntax to send SQL statements and their native language extensions directly to a data source. The DataServer uses the `RUN` `STORED–PROCEDURE` statement with the `send–sql–statement` option to pass SQL statements to the data source. This option gives you access to Transact–SQL, providing you access to business logic for MS SQL Server. For example, you can issue Data Definition Language (DDL) statements from within Progress procedures. You can send multiple SQL statements by concatenating them and passing the string as a single parameter to the `send–sql–statement` option.

> Like the option to define a `RUN STORED-PROC` statement either with or without the `LOAD-RESULT-INTO` options, you can define a send-sql-statement with or without the `LOAD-RESULT-INTO` option. The following section presents and briefly describes a sample use of each technique.

> ##### Without the Load-Result-Into option

> You use the `RUN` `STORED–PROC` statement with the `send–sql–statement` option and pass the Transact-SQL statements as a parameter. The syntax of the statement must be valid Transact-SQL syntax. Example 3–1 shows how this code passes a `SELECT` statement as a parameter.

>     DEFINE VAR handle1 AS INTEGER.
>     RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE
>      ("SELECT name, address, city, state, postal_code
>      FROM customer WHERE credit_limit >= 500").
>     FOR EACH proc-text-buffer WHERE PROC-HANDLE = handle1:
>     	 DISPLAY proc-text-buffer.
>     END.
>     CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1.

> Example 3–1: Passing a SELECT statement as a parameter

> Example 3–1 returns the `name`, `address`, `city`, `state`, and `postal_code` for all `customers` whose `max–credit` is greater than or equal to $500. You must read the results into a buffer as you would with a stored procedure called by a Progress procedure. You can read the results into the `proc–text–buffer` defined by Progress as shown in the example above. Alternatively, you can define your own buffer from within your data source that can accept other data types as well as the `CHARACTER` data type.

> ##### With the Load-Result-Into option

> Example 3–2 shows how to use the send-sql-statement with the `LOAD-RESULT-INTO` option. It also shows that the `PROC-STATUS` phrase must be defined as part of the `RUN STORED-PROC` statement because of the implicit `CLOSE STORED-PROC` that is associated with the `LOAD-RESULT-INTO` phrase.

>     DEFINE VARIABLE res AS INTEGER.
>     DEFINE VARIABLE tthndl AS HANDLE.
>     CREATE TEMP-TABLE tthndl.
>     RUN STORED_PROCEDURE send-sql-statement LOAD-RESULT-INTO tthndl
>        res=PROC-STATUS (“SELECT * FROM customer”).
>     IF res THEN
>        DISPLAY “succeeded”.
>     DISPLAY stat.

> Example 3–2: Using the send-sql-statement with the LOAD-RESULT-INTO option

> Also note in Example 3–2 that the `PROC-STATUS` phrase does not need an associated `PROC-HANDLE` phrase to close the associated procedure because it is retrieved using the `RUN STORED-PROC` statement; although the `PROC-HANDLE` is typically used after the execution of the `RUN STORED-PROC` statement, it is not needed in this context because of the implicit procedure close.

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