You can use something like this:
declare @orderBy varchar(1)
set @orderBy = '2'
SELECT calendar.Eventname, calendar.Calendar_ID, calendar.CalendarDate,
calendar.StartTime, calendar.EndTime, calendar.STAMPM, calendar.ETAMPM,
calendar.Location, calendar.Recurring, calendar.RID, Schools.School_name
FROM calendar INNER JOIN Schools ON calendar.Location = Schools.School_ID
WHERE calendardate >= @CalendarDate
ORDER BY CASE @orderBy WHEN 1 THEN calendar.Eventname
WHEN 2 THEN calendar.CalendarDate
WHEN 3 THEN calendar.Location
ELSE calendar.Eventname
END
HTH,
Dharmendar Kumar
Email/ Fax Communications (http://www.realmagnet.com)
Web Applications (http://www.daksatech.com)
-----Original Message-----
From: Bruce Sorge [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 05, 2007 11:30 AM
To: SQL
Subject: Dynamic Ordery By in Stored Proc
I have a query that has a dynamic order by clause:
SELECT calendar.Eventname, calendar.Calendar_ID, calendar.CalendarDate,
calendar.StartTime, calendar.EndTime, calendar.STAMPM, calendar.ETAMPM,
calendar.Location, calendar.Recurring, calendar.RID, Schools.School_name
FROM calendar INNER JOIN Schools ON calendar.Location = Schools.School_ID
WHERE calendardate >= @CalendarDate
ORDER BY OrderBy
OrderBy is fed from the search results form by clicking on a column header
to sort. How do I write the query in the SP to accept this variable?
Thanks,
Bruce
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Enterprise web applications, build robust, secure
scalable apps today - Try it now ColdFusion Today
ColdFusion 8 beta - Build next generation apps
Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2947
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
