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:[EMAIL PROTECTED]
>Gesendet: Mittwoch, 27. Oktober 2004 14:50
>An: [EMAIL PROTECTED]
>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/[EMAIL PROTECTED]
