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