How can I insert the result set of a procedure into a table (V8)?

[VolkerBarth]

Having written stored procedures for V8 (and below) for years, it's somewhat embarrassing that I'm stuck on this question:

Whereas V9 and above have the handy "SELECT FROM myProcedure()" syntax, V8 has not.

So, within a different stored procedure, when I have a stored procedure myProcedure that returns a result set and I want to insert the result set into a table (here a local temporary one), do I have to use a cursor loop in V8?

I.e. if MyProcedure returns a result set with column MyPk, is a cursor loop like the following the single approach:

begin
   declare local temporary table LT (pk int not null primary key);
   for forCrs as crs
      cursor for call Myprocedure()
      -- for read only -- correction: Not allowed (and not necessary) with call
   do  
      insert LT values(MyPk);
   end loop;
   -- do something with LT's contents   
end;

Sidenote: T-SQL has the INSERT ... EXEC syntax for that but that doesn't seem to work with V8, and it would be infeasible here as I'm dealiung with a Watcom-SQL procedure (of course!).

[Breck_Carter]

8.0.3 seems to be Version Of The Week for me, this week... lemme have a look.

[Breck_Carter]

Yeah, that's how it's done... as far as I know. Maybe someone else has a more satisfying answer. FWIW, however, some Transact SQL statements can be used in a Watcom SQL procedure, and EXECUTE [procedure] is one of them: "The EXECUTE statement is implemented for Transact-SQL compatibility, but can be used in either Transact-SQL or Watcom-SQL batches and procedures."

[VolkerBarth]

@Breck: Thanks for the quick answer! I'm aware of the EXEC[UTE] feature - but "insert LT execute MyProcedure()" gives a syntax error. I think I'm gonna coding the easy for loop, and I guess that's how I have done this all the time before... That "new" V9+ stuff just leads to irritations:)

[Former Member]

@Volker: Perhaps even more annoying, but there is a new procedure in SA12 called sa_copy_cursor_to_temp_table(). It can be used on an open cursor (including one over a procedure with multiple result sets). It was mainly added for internal reasons (spatial viewer in dbisql) but you may wish to consider it as well for those times that you can use SA12.

[VolkerBarth]

@Ivan: Sure, I'm about to migrate to V12, and that procedure is on my radar. Interstingly enough, IIRC, I had some suggestions on that DCX topic in the beta docs...

[VolkerBarth]

@Ivan: Besides the look into the bright future: Are you saying that Breck's conclusion is correct, i.e. that in V8, a cursor has to be used?