--- In [email protected], 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;
