At 07:46 PM 8/12/2011, =?ISO-8859-2?Q?Josef_Koke=B9?= wrote: >Hi! > >It's been my experience that if I have a stored procedure which returns >rows in a particular order and perform a SELECT on this procedure, the >row order of the SELECT matches that of the procedure. E.g.: > >CREATE PROCEDURE test >RETURNS ( value INTEGER ) >AS >BEGIN > value = 1; > SUSPEND; > value = 2; > SUSPEND; > value = 3; > SUSPEND; >END > >SELECT * FROM test > >value >===== >1 >2 >3 > >What I would like to know, is this behavior documented or is it just a >random occurence? I know that when SELECTing from tables/views I should >always use ORDER BY if I need the records sorted in a particular order, >but is the same thing true for selecting from stored procedures?
Rows are returned in the exact order they are sent, i.e., the first SUSPEND sends the first row to the buffer, the second SUSPEND sends the second row, and so on. It is much more efficient to have your SSP work with an ordered set, as the FOR SELECT inside the SP can use indexes. Ordering the set of virtual output is a sloooooow process and of course there are no indexes to call on. ./heLen
