--- In firebird-support@yahoogroups.com, "firebirdsql" <firebirdsql@...> wrote:

>
>
>
> --- In firebird-support@yahoogroups.com, Helen Borrie <helebor@> wrote:
> >
> > At 12:33 PM 7/09/2011, you wrote:
> > >I have a stored procedure that stores some dynamic sql in a string (this is just an example):
> > >CREATE PROCEDURE test RETURNS (return_variable AS VARCHAR(1000))
> > >FOR
> > > SELECT 'SELECT username FROM users ' FROM username INTO :return_variable
> > >DO SUSPEND;
> > >END
> > >
> > >If I then run it:
> > >SELECT return_variable
> > >FROM test ;
> > >
> > >
> > >Instead of returning
> > >'SELECT username FROM users SELECT username FROM users SELECT username FROM users'
> > >
> > >It returns 3 rows of 'SELECT username FROM users'. How do I get it to return a single string?
> >
> > Use a table that has one and only one row. You can use the system table RDB$database for this and don't use FOR SELECT (not needed if the query only expects one row):
> >
> > CREATE PROCEDURE test RETURNS (return_variable AS VARCHAR(1000))
> >
> > SELECT 'SELECT username FROM users ' FROM RDB$DATABASE INTO :return_variable;
> > SUSPEND;
> > END
> >
> > ./hb
> >
>
> The problem is that I need to run several SQL statements inside the stored procedure that concatenates SQL strings and stores it in a variable. At the end of the procedure, I just want to return that string.
>
> When I do the select from RDB$database at the end, firebird complains
> "multiple rows in singleton select"
>
> This is the sql I have:
>
> SELECT 'test' FROM table1 INTO :var1;
> SELECT 'test2' FROM table2 INTO :var2;
>

You have to fill the return value(s), in your case 'return_variable', with the result and suspend:


SELECT 'test' FROM table1 INTO :var1;
SELECT 'test2' FROM table2 INTO :var2;

:return_variable = :var1 || :var2;
SUSPEND;


Good luck
Christian