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