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

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!).

asa-8 stored-procedure insert

asked 29 Oct '10, 09:38

Volker Barth
40.5k●365●556●827
accept rate: 34%

edited 29 Oct '10, 10:05

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

(29 Oct '10, 09:44) 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."

(29 Oct '10, 09:56) Breck Carter

@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:)

(29 Oct '10, 10:01) Volker Barth

@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.

(29 Oct '10, 15:19) Ivan T. Bowman

@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...

(29 Oct '10, 15:35) Volker Barth

@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?

(29 Oct '10, 15:37) Volker Barth

More comments hidden

showing 5 of 6 show all flat view

Be the first one to answer this question!

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

stored-procedure ×125
asa-8 ×34
insert ×34

question asked: 29 Oct '10, 09:38

question was seen: 5,935 times

last updated: 29 Oct '10, 10:05

SAP SQL Anywhere

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

Follow this question

Related questions