Hi Martijn,

Yes I agree - it only has narrow applicability - hence the tentative
suggestion.

Max

> Max,
>
>> I'd rather only have one SP that does everything I want it to do.
>>
>> In any case, IN_ORDER_BY is a varchar(32) variable and not the actual
> column names/aliases.
>>
>> Is it not possible to:
>>
>> SELECT * FROM GET_DATA(IN_ USER_ID) ORDER BY IN_ORDER_BY
>>
>> ie order outside the SP?
>
> Yes, this is possible, but will be much slower.
>
> In you put the ORDER BY and/or WHERE clause inside the procedure,
> it becomes part of the query plan the optimizer can create to get the
> data.
>
> If you put it outside the procedure, Firebird has to first get all rows
> from
> your procedure and then apply the ORDER BY and/or WHERE clauses,
> because a procedure is a "black box" that spits out data rows. The
> optimizer cannot adjust the plan to get the data if you do it this way.
>
> Martijn Tonies
> Database Workbench - development tool for Firebird and more!
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>