Re: Simple question - can't find answer

The result of the proc is actually going to be consumed by SQL Server 2000 (via linked server). I certainly could use a work table, but I was trying to avoid that. I can't have any on disk tables. The Informix (IDS 10) DB is read only. Everett Mills wrote: > Zachary- > Why don't you select your data into a holding table, and have > your procedure return a pointer to it, you would need another table with > just a serial column to make it happen (or if your on 9.X, 10.X you > could use sequences instead): > > CREATE FUNCTION test() > DEFINE > Ser_no INTEGER, > My_rec RECORD LIKE mytable.*> > > INSERT INTO serial_table VALUES (0)> > SELECT dbinfo('sqlca.sqlerrd1') INTO ser_no > FROM serial_table > > DELETE FROM serial_table WHERE serial_no = ser_no> ####This keeps the serial table clean > > INSERT INTO my_output ( SELECT ser_no, * FROM mytable > WHERE my_where_clause )> > RETURN ser_no > > END FUNCTION > > Outside the function you can do something like this (you didn't say what > you were returning to, so I did it as a 4gl): > > Ser_no = test() > > DECLARE foo_curs CURSOR FOR > SELECT * FROM my_output > WHERE serial_key = ser_no> > FOREACH foo_curs INTO whatever > ... > END FOREACH > > DELETE FROM my_output WHERE serial_key = ser_no> > > I didn't test this, but it should give you some ideas. I used something > like it in a 4gl that required a function to return an indefinite number > of values. > > --EEM > > > > > -----Original Message----- > > From: zackary.evans@gmail.com [mailto:zackary.evans@gmail.com] > > Sent: Friday, July 21, 2006 1:45 PM > > To: informix-list@iiug.org > > Subject: Simple question - can't find answer > > > > How do I return a set of data from a stored procedure? Basically all I > > want to do is return the results of a select query which will contain > > multiple rows. > > > > This does not work in informix, it gives error #659. I know it > > basically means i have to select the result into another table, but > > then how does that actually get returned? > > > > CREATE PROCEDURE test()> > > > SELECT * > > FROM table> > > > END PROCEDURE > > > > _______________________________________________ > > Informix-list mailing list > > Informix-list@iiug.org > > http://www.iiug.org/mailman/listinfo/informix-list