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