On 9/5/07, Bruce Sorge <[EMAIL PROTECTED]> wrote: > 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? >
Your best bet will probably be to use a CASE statement...something like ORDER BY CASE @orderby WHEN 'thiscol' THEN thiscol WHEN 'anothercol' THEN anothercol END otherwise you will be building dynamic sql statements and executing them with sp_executesql or EXEC, and that is probably not the best way to go. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2946 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
