--- 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;


SELECT :var1 || :var2 FROM RDB$DATABASE INTO :return_variable;
SUSPEND;