Dynamic Order By with varchar fields.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic Order By with varchar fields.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-05-26 : 08:17:01

Woody writes "First time poster, here and wow what a great resource. Here is my code:

select
a.absentDate as [Date],
a.employeeId as EmpId,
e.LastName+','+e.firstname + '('+ e.employeeViewId + ')' AS Employee,
a.campusID as CampusId,
sc.Name as [Service Center],
case a.shiftId
when 1 then 'AM'
when 2 then 'PM'
end as Shift,
a.usedPersonal as Paid,
a.amtdaymissed as Absent,
a.hours as Hours
from payroll_Absences AS a
join pf_Employees AS e
on e.id=a.employeeid
join payroll_campus AS c
on c.id=a.CampusID
join payroll_employee_shifts as es on
es.employeeId = a.employeeId and
es.campusId = a.campusId and
es.shiftID = a.shiftId
join pf_serviceCenters as SC
on sc.id=e.ServiceCenterID
where
a.Overturned=0 and
AbsentDate BETWEEN convert(char(10),@startDate, 101) +' 00:00:00.000'
and convert(char(10),@endDate, 101) + ' 23:59:59.999' and

-- Search by serviceCenterId if not null
sc.id = isnull(cast(@serviceCenterId as varchar(7)), sc.id) and

-- search by campusId if not null
a.campusId = isnull(cast(@campusId as varchar(12)), a.campusId) and

-- search by employeeId if not null'
a.employeeId = isnull(cast(@employeeId as varchar(14)), a.employeeId)
order by
case
when @orderBy = 1 then a.absentDate
when @orderBy = 2 then e.lastName
when @orderBy = 3 then a.campusId
when @orderBy = 4 then sc.Name
end desc

I am trying to create a dynamic order by, but everytime I try to specify the column as 2 or 4 (both varchar fields) I get this error.

Server: Msg 295, Level 16, State 3, Procedure Payroll_Report_Absences, Line 16
Syntax error converting character string to smalldatetime data type.

I don't know why, the data and id fields work fine, but whenever I try to dynamically sort the aplha data I get the error. Also I would like to be able to take an option such as '1 asc' for my order by variable. I had this working using dynamic sql, but I would like to use a straight SQL call to take advantage of reusable execution paths. Thanks for the help and I hope some of you can shed some light on this for me, thanks."

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-26 : 08:27:12

You will need to have each of the CASE statements seperate.

ORDER BY
CASE WHEN @orderby = '1 ASC' THEN a.absentDate END ASC,
CASE WHEN @orderby = '1 DESC' THEN a.absentDate END DESC,
CASE WHEN @orderby = '2 ASC' THEN e.lastName END ASC,
CASE WHEN @orderby = '2 DESC' THEN e.lastName END DESC

etc, etc

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources