RE: Simple question - can't find answer

Well... I suppose you could return a big*ss char variable and parse it on the other end: CREATE FUNCTION test() DEFINE Ret_val CHAR(3000), My_rec RECORD LIKE mytable.* LET ret_val = "" FOREACH my_select_statement IF ( LENGTH(ret_val > 2900 ) THEN RETURN "Error: too many rows." END IF LET ret_val = ret_val + my_table.var1 + "|" LET ret_val = ret_val + my_table.var2 + "|" ..... LET ret_val = ret_val + "EndLine" + "|" ####Use EndLine to mark the end of each row#### END FOREACH RETURN ret_val END FUNCTION --EEM > -----Original Message----- > From: zackary.evans@gmail.com [mailto:zackary.evans@gmail.com] > Sent: Friday, July 21, 2006 3:02 PM > To: informix-list@iiug.org > Subject: 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 > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list