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

Bwery's gravatar image

Bwery
76448
accept rate: 0%


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.

permanent link

answered 01 May '12, 11:25

Justin%20Willey's gravatar image

Justin Willey
7.0k116149220
accept rate: 21%

edited 01 May '12, 11:27

1

Support for this was added in SA9.

(01 May '12, 11:29) Chris Keating

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

(01 May '12, 11:55) Bwery
Replies hidden

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.

(01 May '12, 15:48) Volker Barth

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.

(01 May '12, 16:58) Bwery

Can you post the batch?

(01 May '12, 17:27) Siger Matt

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

(02 May '12, 16:17) Bwery
showing 2 of 6 show all flat view

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

permanent link

answered 01 May '12, 16:00

Volker%20Barth's gravatar image

Volker Barth
31.5k321462677
accept rate: 32%

Your answer
toggle preview

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

Question tags:

×15

question asked: 01 May '12, 10:19

question was seen: 2,083 times

last updated: 02 May '12, 16:17