I'm trying to allow dynamic ORDER BY clause ordering by passing a
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.

Is the only way to do this to use the result of a UDF??

Code follows:

CREATE PROCEDURE SORT_PROC (
COLUMNLIST VARCHAR(2048),
SORTLIST VARCHAR(1024),
TABLENAME VARCHAR(40),
ALLCOLS CHAR(1))
RETURNS (
ASORTLIST VARCHAR(1024))
AS
DECLARE VARIABLE COLUMNPOS INTEGER;
DECLARE VARIABLE COUNTER INTEGER;
DECLARE VARIABLE TEMPCOLNAME VARCHAR(255);
DECLARE VARIABLE TEMPCHAR CHAR(1);
DECLARE VARIABLE LISTLENGTH INTEGER;
DECLARE VARIABLE SESSIONKEY INTEGER;
DECLARE VARIABLE COLUMNNUM INTEGER;
BEGIN
SESSIONKEY = GEN_ID(GEN_TMPKEYS,1);
/*
The following branch executes if a
SELECT * was performed by the caller
*/
IF (ALLCOLS = 'Y') THEN
BEGIN
COUNTER = 0;
TEMPCOLNAME = '';
ASORTLIST = '';
LISTLENGTH = F_STRINGLENGTH(:SORTLIST);
WHILE (:COUNTER <= :LISTLENGTH) DO
BEGIN
TEMPCHAR = F_STRCOPY(:SORTLIST,:COUNTER,1);
IF (TEMPCHAR <> ',') THEN
BEGIN
TEMPCOLNAME = :TEMPCOLNAME||:TEMPCHAR;
COUNTER = :COUNTER + 1;
END
IF (TEMPCHAR = ',') THEN
BEGIN
SELECT RDB$FIELD_POSITION
FROM RDB$RELATION_FIELDS
WHERE RDB$FIELD_NAME = :TEMPCOLNAME
AND RDB$RELATION_NAME = :TABLENAME
INTO :COLUMNPOS;
COLUMNPOS = :COLUMNPOS + 1;
ASORTLIST = :ASORTLIST||:COLUMNPOS||',';
COUNTER = :COUNTER + 1;
TEMPCOLNAME = '';
END
END
ASORTLIST = F_STRRM(:ASORTLIST,F_STRINGLENGTH(:ASORTLIST));
END

/*
The following executes if the caller explicitly
specified the columns to include in the SELECT statment
*/
IF (ALLCOLS = 'N') THEN
BEGIN
COLUMNNUM = 0;
COUNTER = 0;
TEMPCOLNAME = '';
ASORTLIST = '';
LISTLENGTH = F_STRINGLENGTH(:COLUMNLIST);
WHILE (:COUNTER <= :LISTLENGTH) DO
BEGIN
TEMPCHAR = F_STRCOPY(:COLUMNLIST,:COUNTER,1);
IF (TEMPCHAR <> ',') THEN
BEGIN
TEMPCOLNAME = :TEMPCOLNAME||:TEMPCHAR;
COUNTER = :COUNTER + 1;
END
IF (TEMPCHAR = ',') THEN
BEGIN
COLUMNNUM = :COLUMNNUM + 1;
/*
Insert into TMPKEYS column names and
sequence numbers for later comparison
to columns in sort lists
*/
INSERT INTO TMPKEYS(VARCHAR1,INTEGER1,SESSIONKEY)
VALUES(:TEMPCOLNAME,:COLUMNNUM,:SESSIONKEY);
COUNTER = :COUNTER + 1;
TEMPCOLNAME = '';
END
END

/*
Lookup column names in sort list in TMPKEYS table,
returning the column position in the SELECT statement
*/
COUNTER = 0;
TEMPCOLNAME = '';
ASORTLIST = '';
LISTLENGTH = F_STRINGLENGTH(:SORTLIST);
WHILE (:COUNTER <= :LISTLENGTH) DO
BEGIN
TEMPCHAR = F_STRCOPY(:SORTLIST,:COUNTER,1);
IF (TEMPCHAR <> ',') THEN
BEGIN
TEMPCOLNAME = :TEMPCOLNAME||:TEMPCHAR;
COUNTER = :COUNTER + 1;
END
IF ((TEMPCHAR = ',') OR (:COUNTER >= :LISTLENGTH)) THEN
BEGIN
SELECT INTEGER1
FROM TMPKEYS
WHERE SESSIONKEY = :SESSIONKEY
AND VARCHAR1 = :TEMPCOLNAME
INTO :COLUMNPOS;
/*COLUMNPOS = :COLUMNPOS + 1;*/
ASORTLIST = :ASORTLIST||:COLUMNPOS||',';
COUNTER = :COUNTER + 1;
TEMPCOLNAME = '';
END
END
ASORTLIST = F_STRRM(:ASORTLIST,F_STRINGLENGTH(:ASORTLIST));
END
SUSPEND;
DELETE FROM TMPKEYS
WHERE SESSIONKEY = :SESSIONKEY;
END


CREATE PROCEDURE GET_DUPLICATE_PATIENTS (
PATIENTTYPE INTEGER,
APROVIDERID INTEGER,
DUPTYPE INTEGER,
SORTLIST VARCHAR(1024))
RETURNS (
PRVID INTEGER,
PATLNM VARCHAR(20),
PATFNM VARCHAR(15),
PATDOB DATE,
PATSSN VARCHAR(9),
PATCODE VARCHAR(15),
PATID INTEGER,
PATMNM VARCHAR(1),
PATTYPE INTEGER,
PATNAME VARCHAR(50),
PATLOCID INTEGER,
PATACTIVE VARCHAR(1),
MERGE_VALUE VARCHAR(12),
MERGE_DELETE CHAR(1))
AS
DECLARE VARIABLE PATIENTSSN VARCHAR(13);
DECLARE VARIABLE TMPPRVID INTEGER;
DECLARE VARIABLE SESSIONKEY INTEGER;
DECLARE VARIABLE COLUMNLIST VARCHAR(2048);
DECLARE VARIABLE ASORTLIST VARCHAR(255);
BEGIN
SESSIONKEY = GEN_ID(GEN_TMPKEYS,1);
SELECT COUNT(*)
FROM PATIENT
WHERE PRVID = :APROVIDERID
INTO :TMPPRVID;
IF ((DUPTYPE = 0) OR (DUPTYPE = 2)) THEN /* Duplicate Patient */
BEGIN
IF (:TMPPRVID > 0) THEN
BEGIN
INSERT INTO TMPKEYS(TEMP_PKEY,VARCHAR1,SESSIONKEY)
SELECT D1.PATID,D1.PATNAME,:SESSIONKEY
FROM PATIENT D1, PATIENT D2
WHERE (D1.PATLNM = d2.PATLNM)
AND (d1.PATFNM = d2.PATFNM)
AND (D1.PATTYPE = :PATIENTTYPE)
AND (d2.PATTYPE = :PATIENTTYPE)
AND (d1.PATLNM > '')
AND (d2.PATLNM > '')
AND (d1.PATFNM > '')
AND (d2.PATFNM > '')
AND (D1.PRVID = :APROVIDERID)
AND (D2.PRVID = :APROVIDERID)
GROUP BY D1.PATID,D1.PATNAME/*,D1.PATLNM,D1.PATFNM*/
HAVING COUNT(*) > 1;
END

IF (:TMPPRVID = 0) THEN
BEGIN
INSERT INTO TMPKEYS(TEMP_PKEY,VARCHAR1,SESSIONKEY)
SELECT D1.PATID,D1.PATNAME,:SESSIONKEY
FROM PATIENT D1, PATIENT D2
WHERE (D1.PATTYPE = :PATIENTTYPE)
AND (D1.PATLNM = d2.PATLNM)
AND (d1.PATFNM = d2.PATFNM)
AND (d2.PATTYPE = :PATIENTTYPE)
AND (d1.PATLNM > '')
AND (d2.PATLNM > '')
AND (d1.PATFNM > '')
AND (d2.PATFNM > '')
GROUP BY D1.PATID,D1.PATNAME,D2.PATLNM,D2.PATFNM
HAVING COUNT(*) > 1;
END
END

IF ((DUPTYPE = 1) OR (DUPTYPE = 2)) THEN /* Duplicate SSN */
BEGIN
IF (:TMPPRVID > 0) THEN
BEGIN
FOR SELECT P.PATSSN FROM PATIENT P
WHERE (P.PATSSN > '')
AND (P.PATTYPE = :PATIENTTYPE)
AND (P.PRVID = :APROVIDERID)
GROUP BY P.PATSSN
HAVING COUNT(*) > 1
INTO :PATIENTSSN
DO
BEGIN
INSERT INTO TMPKEYS(TEMP_PKEY,VARCHAR1,SESSIONKEY)
SELECT DISTINCT D1.PATID,D1.PATSSN,:SESSIONKEY
FROM PATIENT D1
WHERE (D1.PATSSN = :PATIENTSSN);
END
END

IF (:TMPPRVID = 0) THEN
BEGIN
FOR SELECT P.PATSSN FROM PATIENT P
WHERE (P.PATSSN > '')
AND (P.PATTYPE = :PATIENTTYPE)
GROUP BY P.PATSSN
HAVING COUNT(*) > 1
INTO :PATIENTSSN
DO
BEGIN
INSERT INTO TMPKEYS(TEMP_PKEY,SESSIONKEY)
SELECT DISTINCT D1.PATID,:SESSIONKEY
FROM PATIENT D1
WHERE (D1.PATSSN = :PATIENTSSN);
END
END
END
COLUMNLIST = 'PRVID,PATLNM,PATFNM,PATDOB,PATSSN,PATCODE,PATID,'||
'PATMNM,PATTYPE,PATNAME,PATLOCID,PATACTIVE,'||
'MERGE_VALUE,MERGE_DELETE';
/*
SELECT ASORTLIST
FROM SORT_PROC(:COLUMNLIST,:SORTLIST,'PATIENT','N')
INTO :ASORTLIST;
*/

FOR SELECT
PRVID,PATLNM,PATFNM,PATDOB,PATSSN,
PATCODE,PATID,PATMNM,PATTYPE,PATNAME,
PATLOCID,PATACTIVE,
CAST('' AS VARCHAR(12)) AS MERGE_VALUE,
CAST('N' AS CHAR(1)) AS MERGE_DELETE
FROM PATIENT, TMPKEYS
WHERE (PATIENT.PATID = TMPKEYS.TEMP_PKEY)
AND ((PATNAME > '') OR (PATSSN > ''))
ORDER BY SORT_PROC(:COLUMNLIST,:SORTLIST,'PATIENT','N')
/*(SELECT :ASORTLIST FROM RDB$RELATIONS ROWS 1)*/
/*PATLNM,PATFNM,PATDOB,PATSSN,PATCODE,PATID,PRVID*/
INTO :PRVID,:PATLNM,:PATFNM,:PATDOB,:PATSSN,:PATCODE,:PATID,
:PATMNM,:PATTYPE,:PATNAME,:PATLOCID,:PATACTIVE,:MERGE_VALUE,
:MERGE_DELETE
DO
SUSPEND;

DELETE FROM TMPKEYS
WHERE SESSIONKEY = :SESSIONKEY;
END