I'm trying to order a stored procedure dynamically using a variable called @OrderBy in a CASE statement on the ORDER BY clause, but i'm continuosly getting this error:
"Error 145: ORDER BY items must appear in the select list if SELECT DISTINCT is specified."
Here's my code:
CREATE PROCEDURE dbo.sp_det_eq_chasis
-- Detalle de Equipos por Tipo de Chasis
@tipochasis varchar(100), @OrderBy varchar(100)
AS
SELECT DISTINCT cs.name0 'Computadora',
ISNULL(ctd.ChassisTypesDesc0,'_Sin Tipo Chasis_') AS 'Tipo Chasis',
username0 'Usuario-Inventario',
user_name0 'Usuario-Last-Logon',
cs.manufacturer0 'Fabricante',
model0 'Modelo',
pr.name0 'Procesador',
CONVERT(varchar(25),CurrentClockSpeed0) 'Velocidad'
FROM GS_COMPUTER_SYSTEM cs
LEFT OUTER JOIN GS_PROCESSOR
pr ON cs.resourceid=
pr.resourceid
LEFT OUTER JOIN SMS_CHASSIS_TYPES_DESCRIPTIONS ctd
ON cs.chassistypes0=ctd.chassistypes0
WHERE ISNULL(ctd.ChassisTypesDesc0,'_Sin Tipo Chasis_') LIKE RTRIM(@tipochasis)+ '%'
ORDER BY
CASE
WHEN @OrderBy='Computadora' THEN cs.name0
WHEN @OrderBy='Tipo Chasis' THEN ISNULL(ctd.ChassisTypesDesc0,'_Sin Tipo Chasis_')
WHEN @OrderBy='Usuario-Inventario' THEN cs.username0
WHEN @OrderBy='Usuario-Last-Logon' THEN cs.user_name0
WHEN @OrderBy='Fabricante' THEN cs.manufacturer0
WHEN @OrderBy='Modelo' THEN model0
WHEN @OrderBy='Procesador' THEN
pr.name0
WHEN @OrderBy='Velocidad' THEN CONVERT(varchar(25),CurrentClockSpeed0)
ELSE cs.name0
END
GO
When i try it without the DISTINCT word it works fine, i have alias for each column and put that alias after the THEN word and it still won't work.
Can anyone help?
Thank You!
TANIA P. BAQUERO
Panama Canal Authority
Student