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
