Stored Procedures

If using Microsoft SQL Server, stored procedures can be called via embedded SQL. Some restrictions apply. See Restrictions for more information.

Generally, you can execute two types of stored procedures: those that return no result sets, and those that return a single result set. If you execute stored \procedures that return more than one result, the results are not available to the COBOL program. AcuSQL does support output parameters and return code values, however.

Consider the following stored procedure that has one output parameter and also returns a value:

create procedure sp_listcustomer
    @lastname varchar(100) = NULL,
    @numrows int output
    as
select @numrows = count(*) from customer where c_last_name = @lastname
select c_last_name, c_first_name, c_birthday from customer
    where c_last_name = @lastname
return 23

This stored procedure returns a single result set (c_last_name, c_first_name, and c_birthday). Depending on the rows in the customer table, this can consist of many rows.

There are two ways to execute this procedure from a COBOL program:

Ignore the result sets, and just use the output parameters and return code.
This can be done with the following code:
```
display "Enter the name to search for: ", no.
accept c-last-name.
EXEC SQL exec :ret-code = exec sp_listcustomer
(:c-last-name, :num-rows out)
END-EXEC.
```
Note that c-last-name, ret-code, and num-rows need to have been declared as valid variables in a DECLARE section. When executed this way, all the rows returned by the stored procedure are thrown away, and only the num-rows variable and the ret-code variable are changed. ret-code will be the value 23 (based on the return 23 in the stored procedure)and num-rows will be the number of rows that match the

WHERE.
Have the result set returned to the COBOL program. This requires a cursor to be declared, and is done with the following code:
```
display "Enter the name to search for: ", no.
accept c-last-name.
EXEC SQL declare spcursor cursor for
        :ret-code = exec sp_listcustomer (:c-last-name,
         :num-rows out)
END-EXEC.

EXEC SQL
    open spcursor
END-EXEC.

move 0 to num-rows-read.
perform until SQLCODE not = 0
    EXEC SQL
        FETCH spcursor into
         :c-last-name, :c-first-name,
         :c-birthday
    END-EXEC
    if SQLCODE = 0
        add 1 to num-rows-read
        display c-last-name, ", ", c-first-name, ", ",
               c-birthday
    end-if
end-perform.
if num-rows not = num-rows-read
    display "stored procedure error, " num-rows,
            " not = ", num-rows-read
end-if.
```
Note that c-last-name, ret-code, num-rows, c-first-name, and c-birthday need to have been declared as valid variables in a DECLARE section. When executed this way, the num-rows and ret-code variables are set to the values given by the stored procedure at the time the cursor is opened. You must then execute FETCH commands in order to get the result set columns. This particular example tests the number of rows actually fetched against the num-rows value returned by the stored procedure.

There are two types of syntax allowed by the precompiler when MSSQL syntax is in effect (that is, when you specify the -Pk mssql option).
```
EXEC SQL [:status-var = ] EXEC procedure-name
[[:param-var [out[put]]],...]
```
and
```
EXEC SQL DECLARE cursor-name CURSOR FOR
[:status-var = ] EXEC procedure-name [[:param-var
[out[put]]], ...]
```
The second form requires all the usual steps necessary for cursors: you must open the cursor and then fetch from it until all the rows have been fetched.

Any output parameters, including the return value, are not returned to the COBOL program until an UNPREPARE or CLOSE (for cursors) is executed.