SORT BY column# in stored procedure

SORT BY column# in stored procedure

Post by Thom » Wed, 09 Jul 2003 07:55:03



I am trying to pass a column# as a parameter to a stored procedure, so
it can sort the result by that column. However Stored procedure always
take that number as a column name. Does someone know a walk around?

Thanks,

 
 
 

SORT BY column# in stored procedure

Post by Jonathan Leffle » Wed, 09 Jul 2003 12:54:35



> I am trying to pass a column# as a parameter to a stored procedure, so
> it can sort the result by that column. However Stored procedure always
> take that number as a column name. Does someone know a walk around?

If you're using IDS 9.x, you can download and install the Dynamic SQL
Bladelet from the IIUG.

Otherwise, your stuck.  What you're after is Dynamic SQL; what SPL
does not support is Dynamic SQL.

--
Jonathan Leffler                   #include <disclaimer.h>

Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

 
 
 

1. Using a Stored Procedure to sort and update a column

I have a table I'd like to keep ordered, with no gaps in one of the columns.
Let's say it's a three-column table and looks like this:

OptionID            OptionOrder               OptionLabel
---------             -------------                ------------

4453                    1                                Some text here
5532                    2                                More text here (not
a large field)
8811                    3                                Still More text
6623                    4                                etc
7611                    4                                etc
2187                    5                                etc

What I'm looking to do first is select the columns, sorted by OptionOrder
and OptionLabel.  Then, I want to check to see that the same OptionOrder
isn't listed twice-- so that for instance, there won't be any duplicate "4"s
above.

In other words, I'd like to keep the ordering the way it is, but update the
OptionOrder column so that it's sequential, with no duplicates. (and
preferably, no gaps either)  If someone could point me in the right
direction on this I'd be grateful, and thanks.

2. Restrictions on "InLine" Views in Procedures

3. store a file via stored procedure to an image column

4. printing the ISQL result set

5. Calling a Java Stored Procedure from another Java Stored Stored Procedure

6. simple newbie question - writing a connection string

7. Sorting Stored procedure using a parameter

8. DTS data conversion

9. stored procedure for paging, with sorting

10. Stored Procedure: Specifying ASC or DESC sort order through a parameter

11. dynamic sorting in stored procedure

12. Passing Sort Parameter to Stored Procedure

13. Sorting by Variable in Stored Procedure