| Subject | Re: [firebird-support] ORDER BY not working?? |
|---|---|
| Author | Helen Borrie |
| Post date | 2008-04-09T00:26:19Z |
At 07:49 AM 9/04/2008, you wrote:
If you are using Fb 1.5 or higher, you can generate a run-time statement string inside your SP using EXECUTE STATEMENT. This might help you achieve what you want. It is described in detail in the v.1.5 release notes.
As a further urgent piece of advice, it is very unwise to write stored procedures that mix execution of data state changing operations with constructing output sets. Define your data state changing operations in executable SPs and your set-returning operations in selectable SPs. Not only are you returning a set that's inconsistent with the state of the database, but any "refresh" operation will re-execute all of the DML code.
You should also try to avoid writing "monumental" SPs. Try to design them modularly and call them from within the "base" SP using EXECUTE [args..]...[returning_values]. Apart from the structural ugliness that makes monumental SPs hard to troubleshoot, there are limits on the total number of bytes (48K) that can be stored in the BLR (the executable code that is created by the engine at compile time for execution at run-time). At run-time the total length of the BLR is augmented by the total sizes of actual data being carried around in parameters, meaning it's not too hard to tip over the maximum size of an SQL statement (64K).
In complex executable SP code you should also be building in exception handling.
./heLen
>I'm trying to allow dynamic ORDER BY clause ordering by passing aThis is "as designed". PSQL is not a scripting tool for designing output structures dynamically at run-time, it's a mechanism for processing data. Sets on which the code operates are structurally static and you can't manipulate them into a different structure. Field lists and sorting criteria (ORDER BY GROUP BY criteria) are static.
>computed column list on the client as a varchar into a stored proc,
>and using that parameter to sort the result set accordingly.
>
>So far I've tried to use the input param with the column names, I've
>tried returning the sort list as the RETURN parameter of a stored
>proc, I've converted the columns into their corresponding numbers
>within the select column list etc.
>
>No matter what I do I can get the order by clause to use an expression
>from a stored proc result nor a passed in column list.
If you are using Fb 1.5 or higher, you can generate a run-time statement string inside your SP using EXECUTE STATEMENT. This might help you achieve what you want. It is described in detail in the v.1.5 release notes.
As a further urgent piece of advice, it is very unwise to write stored procedures that mix execution of data state changing operations with constructing output sets. Define your data state changing operations in executable SPs and your set-returning operations in selectable SPs. Not only are you returning a set that's inconsistent with the state of the database, but any "refresh" operation will re-execute all of the DML code.
You should also try to avoid writing "monumental" SPs. Try to design them modularly and call them from within the "base" SP using EXECUTE [args..]...[returning_values]. Apart from the structural ugliness that makes monumental SPs hard to troubleshoot, there are limits on the total number of bytes (48K) that can be stored in the BLR (the executable code that is created by the engine at compile time for execution at run-time). At run-time the total length of the BLR is augmented by the total sizes of actual data being carried around in parameters, meaning it's not too hard to tip over the maximum size of an SQL statement (64K).
In complex executable SP code you should also be building in exception handling.
>Is the only way to do this to use the result of a UDF??God forbid.
./heLen