Can you dump stored procedure results to a flat file?

[Former Member]

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.

[justin_willey · Accepted solution]

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.

[chris_keating · Accepted solution]

Support for this was added in SA9.

[Former Member · Accepted solution]

It seems that the UNLOAD command can't be run with other commands, it has to be the only one. Is this true?

[VolkerBarth · Accepted solution]

What exactly do you mean by "it has to be the only one"?

UNLOAD (in contrast to ISQL's OUTPUT) is not a command but a SQL statement and as such surely can be used in statement blocks, i.e. in SQL batches, procedures and the like.

[Former Member · Accepted solution]

When I add it to a procedure (which is what I initially wanted to do) it gave me an error when I tried to compile the proc (-131 Syntax error new 'UNLOAD' on line 59). I tried to run the SQL as a batch instead and got the same error.

[former_SQLA_member1694868 · Accepted solution]

Can you post the batch?

[Former Member · Accepted solution]

Looks like I needed use use semi-colons after each statement. It seems to be working as a batch now.

[VolkerBarth]

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