From: Filip Sergeys (filip.sergeysverzekeringen.be)
Date: Wed Apr 20 2005 - 08:50:12 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

There are a number of repetitive actions to be taken on result tables in
different stored procedures.

I wanted to create a function to which I can pass a reference to a
result table name.
The result table is created in the calling stored procedure with
"declare <result_table_name> cursor for select .... for reuse;"
call <function_name>(<result_table_name>,<action_to_do>);
Then in the function open <result_table_name>, fetch every record, do my
stuff, and store it in a new cursor which is returned to the calling
stored procedure.

Example: pass result table to a function, order it and then fetch only
10 first records:

==========
create function limitresult (cursorname char(20), limit fixed(2))
returns cursor as

var counter fixed(2);
set counter = 0;
$CURSOR = 'limitedresult';
try
   open cursor :cursorname;
   while (counter < :limit) do begin
       fetch into :$cursor;
       set counter = counter + 1;
   end;
   return :$cursor;
catch
   if $rc <> 100 then stop ($rc, 'sorry, it didn't work out!');
==========

As you might expect, this doesn't work, how can I reach my goal? I can't
find the answer in the documentation.

Thanx in advance

Regards,

Filip

--
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
* System Engineer, Verzekeringen NV *
* www.verzekeringen.be *
* Oostkaai 23 B-2170 Merksem *
* 03/6416673 - 0477/340942 *
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]