Use ORDER BY in a Stored Procedure with UNION

  • Thread starter Thread starter Lauren Quantrell
  • Start date Start date
L

Lauren Quantrell

I have a stored procedure with four select statements joined by UNION.
How do I use ORDER BY to sort the whole result?
I want to be able to sort by fldMyField, fldMyOtherField as well as
myCalcDate.

(by the way, the following SP works well to sort through annual,
monthly, weekly and single date reminders, with much assistance from
user Chuck Conover)

Alter procedure SPmyReminders
@DateNow datetime

as
begin

/* select statement for annual events follows */

select
tblMyTableName.fldMyField,
dateadd(yy,datediff(yy,fldMyDate,@DateNow),fldMyDate) AS myCalcDate,
tblMyTableName.fldMyOtherField
FROM
tblMyTableName
WHERE
@DateNow between dateadd(dd,(-1)*ReminderDays,dateadd(yy,datediff(yy,fldMyDate,@DateNow),fldMyDate))
and dateadd(yy,datediff(yy,fldMyDate,@DateNow),fldMyDate)


union

/* select statement for monthly events follows */

select
tblMyTableName.fldMyField,
dateadd(mm,datediff(mm,fldMyDate,@DateNow),fldMyDate) AS myCalcDate,
tblMyTableName.fldMyOtherField
FROM
tblMyTableName
WHERE
@DateNow between dateadd(dd,(-1)*ReminderDays,dateadd(mm,datediff(mm,fldMyDate,@DateNow),fldMyDate))
and dateadd(mm,datediff(mm,fldMyDate,@DateNow),fldMyDate)


union

/* select statement for weekly events follows */

select
tblMyTableName.fldMyField,
dateadd(ww,datediff(ww,fldMyDate,@DateNow),fldMyDate) AS myCalcDate,
tblMyTableName.fldMyOtherField
FROM
tblMyTableName
WHERE
@DateNow between dateadd(dd,(-1)*ReminderDays,dateadd(ww,datediff(ww,fldMyDate,@DateNow),fldMyDate))
and dateadd(ww,datediff(ww,fldMyDate,@DateNow),fldMyDate)


union

/* select statement for non-recurring events follows */

select
tblMyTableName.fldMyField,
fldMyDate AS myCalcDate,
tblMyTableName.fldMyOtherField
FROM
tblMyTableName
WHERE
@DateNow between dateadd(dd,(-1)*ReminderDays,fldMyDate) and fldMyDate

end
 
Just add your ORDER BY statement to the end of last
SELECT statement
i.e.

select
tblMyTableName.fldMyField,
fldMyDate AS myCalcDate,
tblMyTableName.fldMyOtherField
FROM
tblMyTableName
WHERE
@DateNow between dateadd(dd,(-1)*ReminderDays,fldMyDate)
and fldMyDate
ORDER BY fldMyField,fldMyOtherField,fldMyDate DESC
 
uh you cant in SQL 2000 (unless you use some cheesy SQL concatenating cheat)

but that defeats the purpose of using a stored proc, as it has like 0
optimization and compilation and all the other benefits.

cant you do a clientside sort in the ADP itself?
 
Back
Top