Re: SPL Returning Column Names

As far as I can see, you can't do it. You can only return multiple values from a stored procedure in an EXECUTE PROCEDURE statement (if you use a stored procedure in, for example, a SELECT statement, it must return a single row containing a single column). You have to declare a cursor for a stored procedure which might return more than one row; the DESCRIBE statement only says '(expression)' as the name of the returned values, so you can't give a suitable name to the returned values. It isn't clear what names you would give. Consider a bloody-minded stored procedure such as: CREATE PROCEDURE awkward_squad() RETURNING INTEGER, INTEGER; DEFINE i, j INTEGER; LET i = 1; LET j = 2; RETURN i, j WITH RESUME; RETURN j, i WITH RESUME; RETURN 0, -1 WITH RESUME; RETURN i, -1 WITH RESUME; RETURN 0, j WITH RESUME; RETURN j, -1 WITH RESUME; RETURN 0, i WITH RESUME; RETURN i, i WITH RESUME; RETURN j, j WITH RESUME; END PROCEDURE; Should the first returned column be called i, j, or something else? Why? Ditto for the second? Note that I could have written those RETURN statements in any sequence -- would that change your answer? It could, of course, give you arbitrary names, such as 'value1', 'value2', but that wouldn't benefit you in any significant way. Of course, if the syntax were extended to allow the following syntax (which would, preferably, declare local variables value1 and value2), then the names of the return values would be unambiguous and there'd be no problem returning the proper names. However, the present syntax does not allow this, so you are stuck with anonymous return values. CREATE PROCEDURE awkward_squad() RETURNING value1 INTEGER, value2 INTEGER;... END PROCEDURE; Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> >From: "sco" <sco@choicehotels.com> >Date: 8 Oct 1996 17:24:19 GMT >X-Informix-List-Id: <news.29035> > >Thanks for the reply. > >Unfortunately the problem is not with the syntax I am using. The problem >is that there is no column names associated with fname, nrows. I get the >returned data just fine. > >Normally if you did a select fname, nrows from table. The returned data >would have a column name of 'fname' and 'nrows' that is associated with the >data. In a stored procedure only the values are returned, their is no name >associated with the values. This is what prompted my question: Is there >anyway to return data and a column name for that data from a stored >procedure? > >Thanks >--Sco > >Dennis Robinson <drrobin@vsol.com> wrote in article ><53b2jg$jme@cssun.mathcs.emory.edu>... >> >> ---------- >> From: Charles Smith[SMTP:csmith@vsol.com] >> Sent: Saturday, October 05, 1996 3:07 PM >> To: 'Dennis Robinson' >> Subject: FW: SPL Returning Column Names >> >> Dennis: >> When you create a stored procedute the next line should look like this >> ' RETURNING CHAR(15), INT;' >> other stuff >> >> at the return should look like >> 'RETURN fname,nrows' >> >> Example on page 12-26 SQL Tutorial Version 7.2 >> >> Hope this helps >> Charles.