I know I can use the UNLOAD command to pipe the results of a SELECT statement to a file. How can I do the same with the results that are returned from a stored procedure? I'm trying to create this in an SQL Anywhere 8 and 9 database.
asked 01 May '12, 10:19
If you can't do
select * from mystoredprocedure(param1, param2);
which you can't in 8 (but I'm not sure about 9), you can use a cursor to select each line from the sp result into a temporary table, and then unload the table. Clunky, but it works. UNTESTED:
declare local temporary table MyTempTable(orderno int, ValText long varchar) create variable LoopCounter integer; set LoopCounter=0; for GetText as TextCursor no scroll cursor for call sa_validate() for read only do insert into MyTempTable(orderno,ValText) values (LoopCounter, ValidationText); set LoopCounter=LoopCounter+1 end for; select * from MyTempTable order by orderno;
v10 onwards there is no problem as you can directly select the rows returned by the procedure.
AFAIK, as Justin has told, for v8 and older versions, if you need to access the result set of a stored procedure from within the database engine, you need a cursor loop to do so. The FOR LOOP (as in Justin's sample) is a handy way to do so. Thus requirement is not restricted to UNLOAD but is true for any other need to access the result set. (Of course a cursor loop is not necessary when trying to call a stored procedure from a client application!)
v9 has introduced the facility to use prodecure calls in the FROM clause, which has been a great enhancement.
FWIW, MS SQL Server's Transact-SQL dialect (at least in older versions - and if IIRC) has an "INSERT ... EXEC proc" statement that allows to fetch the result set in a (temporary) table - but I don't think that has ever been ported to SQL Anywhere (and it would be rather limited in contrast to the "FROM proc()" enhancement).
answered 01 May '12, 16:00