Problem:

My SQL Server stored procedure  takes a list of parameters and returns a result set what is the required syntax to call this stored procedure?

Resolution:

from docs:

If a stored procedure call returns a result set, it must be used in a cursor declaration, thus:

    EXEC SQL

        DECLARE cursorName CURSOR FOR storedProcecureCall

The stored procedure is then called by OPENing the cursor and FETCHing result set rows, like any other type of cursor.

Currently OpenESQL supports only a single result set.

The code for calling a stored procedure that has two input parameters and returns a result set containing 3 output values for each fetched row would be something like the follows:

          exec sql

              declare c1 cursor for

                  call msstorproc(:param1, :param2)

          end-exec

          exec sql

              open c1

          end-exec

           perform until sqlcode < 0 or sqlcode  = 100

               exec sql

                   fetch c1 into :retvar1, :retvar2, :retvar3

               end-exec

                   display retvar1 " " retvar2 " " retvar3          

           end-perform