Thursday, March 22, 2012

Dynamic ORDER BY within stored procedure

I am trying to do something similar to the following where I want to perform dynamic ordering on two tables that have been unioned as shown below.


CREATE PROCEDURE procedure_name
@.regNum varchar(14),
@.sortOrder tinyint = 1
AS
SELECT Filler_OrdNum As 'Accession', RTrim(Obs_Code) As 'Observation', REG As 'Register',
Obs_Date As 'Observation Date'
FROM tblSPG_Header
WHERE
REG = @.regNum
UNION
SELECT Filler_OrdNum As 'Accession', RTrim(Obs_Code) As 'Observation', REG As 'Register',
Obs_Date As 'Observation Date'
FROM tblRCH_Header
WHERE
REG = @.regNum
ORDER BY Obs_Date DESC
GO

Note that I am only sorting on the Obs_Date column, but I'd like to be able to sort on any column within the selection list. I know that I need to use:


ORDER BY CASE WHEN @.sortOrder = 1 THEN Obs_Date END DESC

but I frequently get the following error when I try to do so:

"ORDER BY items must appear in the select list if the statements contain a UNION operator"

If anyone can offer any suggestions, I would appreciate it. Thanks.try something like :


EXEC ( ' SELECT Filler_OrdNum As ''Accession'', RTrim(Obs_Code) As ''Observation'', REG As ''Register'',Obs_Date As ''Observation Date''
FROM tblSPG_Header WHERE REG = ' + @.regNum + ' order by ' + @.ordcolumn +
' UNION
SELECT Filler_OrdNum As ''Accession'', RTrim(Obs_Code) As ''Observation'', REG As ''Register'',
Obs_Date As ''Observation Date'' FROM tblRCH_Header WHERE REG = ' + @.regNum + ' order by ' + @.ordcolumn )

hth

No comments:

Post a Comment