>> --- 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; >
Btw, with Firebird 2.5, also the following variable assignment is possible: var = (select 'test' from rdb$database); Just an example. Might be more readable than the INTO clause. -- With regards, Thomas Steinmaurer * Upscene Productions - Database Tools for Developers http://www.upscene.com/ * My Blog http://blog.upscene.com/thomas/index.php * Firebird Foundation Committee Member http://www.firebirdsql.org/en/firebird-foundation/
