--- In [email protected], "firebirdsql" <firebirdsql@...> wrote: > > > > --- 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; >
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
