AW: Can I create a generic stored procedure to sort any column pa ssed as input parameter???

Hi Sanjay,
unfortunately you didn't tell us which error occurred.
I tried your procedure and did not receive an error.
However, the result set has not been sorted as expected, because
order by :columnNo is not interpreted as 'order by column :columnNo'
but is interpreted as 'order by literal :columnNo'.
You have to use dynamic sql inside the procedure to solve the problem :

CREATE DBPROC SORTCOLUMN(IN COLUMNNO INT) RETURNS CURSOR AS

VAR 
  statement CHAR(200);

$CURSOR   = 'RETURNCURSOR';
statement = 'DECLARE RETURNCURSOR CURSOR FOR ' ||
      'SELECT JOBS.JOBID, JOBS.JOBNO, JOBS.FOLDERNAME, JOBS.STARTTIME ' || 
      'FROM PMP_OWNER.JOBS ORDER BY ' || CHR(columnNo);
execute statement;

Best Regards,
Thomas


>-----Ursprüngliche Nachricht-----
>Von: sanjay soni [mailto:mailtobablu@xxxxxxxxx] 
>Gesendet: Mittwoch, 27. Oktober 2004 14:50
>An: maxdb@xxxxxxxxxxxxxxx
>Betreff: Can I create a generic stored procedure to sort any 
>column passed as input parameter???
>
>
>
>Hi, 
>
>I have been trying to write a generic stored procedure to sort 
>any column number passed as input parameter to the stored procedure.
>
>I have to think of this, because there are total 10 columns in 
>the table and we want sorting on each column. We can write 
>separate stored procedure for each column to sort on, but that 
>is a huge work and not the good solution. Hence I tried to 
>write a generic stored procedure. But somehow this is not 
>working and though it compiles properly. But when I try to 
>call it, I get the general error.
>
>One thing I would like to point out that we dont want to move 
>this to programming task and want to do everything at the db 
>layer side, so that performance of the system wont affect.
>
>Can anybody let me know whether it is possible to write a 
>generic stored procedure to sort any column passed as input 
>parameter????
>
>If yes, please let me know.
>
>If not, is there any other way of doing all this????
>
>Well here is the code of that generic stored procedure.
>
>CREATE DBPROC SORTCOLUMN(IN COLUMNNO INT) RETURNS CURSOR AS
>
>$CURSOR = 'ReturnCursor';
>
>DECLARE $CURSOR CURSOR FOR
>
>SELECT JOBS.JOBID, JOBS.JOBNO, JOBS.FOLDERNAME, JOBS.STARTTIME 
> FROM PMP_OWNER.JOBS ORDER BY :columnNo;
>
>
>               
>---------------------------------
>Do you Yahoo!?
>Yahoo! Mail Address AutoComplete - You start. We finish.
>

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=gcdm-maxdb@xxxxxxxxxxx