sort - T-SQL Conditional Order By

sql server descending order

Question

Estoy intentando escribir un procedimiento almacenado que devuelve una lista de objetos con el orden de clasificación y la dirección de ordenamiento seleccionados por el usuario y pasados ​​como parámetros sql.

Digamos que tengo una tabla de productos con las siguientes columnas: product_id (int), name (varchar), value (int), created_date (datetime) y los parámetros @sortDir y @sortOrder

Quiero hacer algo como

select *
from Product
  if (@sortOrder = ''name'' and @sortDir = ''asc'') 
  then order by name asc
  if (@sortOrder = ''created_date'' and @sortDir = ''asc'') 
  then order by created_date asc
  if (@sortOrder = ''name'' and @sortDir = ''desc'') 
  then order by name desc
  if (@sortOrder = ''created_date'' and @sortDir = ''desc'') 
  then order by created_date desc

Intenté hacerlo con declaraciones de casos, pero estaba teniendo problemas ya que los tipos de datos eran diferentes. Alguien tiene alguna sugerencia?

Answer 1

Hay múltiples formas de hacer esto. Una forma sería:

SELECT *
FROM
(
  SELECT
  ROW_NUMBER() OVER ( ORDER BY
  CASE WHEN @sortOrder = ''name'' and @sortDir = ''asc'' then name
  END ASC,
  CASE WHEN @sortOrder = ''name'' and @sortDir = ''desc'' THEN name
  END DESC,
  CASE WHEN i(@sortOrder = ''created_date'' and @sortDir = ''asc'' THEN created_date
  END ASC,
  CASE WHEN i(@sortOrder = ''created_date'' and @sortDir = ''desc'' THEN created_date
  END ASC) RowNum
  *
)
order by 
RowNum

También puede hacerlo utilizando sql dinámico.

Answer 2

Necesita una declaración de caso, aunque usaría varias declaraciones de casos:

order by (case when @sortOrder = ''name'' and @sortDir = ''asc'' then name end)  asc,
         (case when @sortOrder = ''name'' and @sortDir = ''desc'' then name end) desc,
         (case when @sortOrder = ''created_date'' and @sortDir = ''asc'' then created_date end) asc,
         (case when @sortOrder = ''created_date'' and @sortDir = ''desc'' then created_date end) desc

Tener cuatro cláusulas diferentes elimina el problema de convertir entre tipos.

Answer 3

CASE es una expresión que devuelve un valor. No es para control de flujo, como IF . Y no puedes usar IF dentro de una consulta.

Desafortunadamente, hay algunas limitaciones con las expresiones CASE que hacen que sea engorroso hacer lo que desea. Por ejemplo, todas las ramas en una expresión CASE deben devolver el mismo tipo o ser implícitamente convertibles al mismo tipo. No probaría eso con cadenas y fechas. Tampoco puede usar CASE para especificar la dirección de clasificación.

SELECT column_list_please
FROM dbo.Product -- dbo prefix please
ORDER BY 
  CASE WHEN @sortDir = ''asc'' AND @sortOrder = ''name'' THEN name END,
  CASE WHEN @sortDir = ''asc'' AND @sortOrder = ''created_date'' THEN created_date END,
  CASE WHEN @sortDir = ''desc'' AND @sortOrder = ''name'' THEN name END DESC,
  CASE WHEN @sortDir = ''desc'' AND @sortOrder = ''created_date'' THEN created_date END DESC;

Una solución discutible más fácil (especialmente si esto se vuelve más complejo) es usar SQL dinámico. Para frustrar la inyección de SQL puedes probar los valores:

IF @sortDir NOT IN (''asc'', ''desc'')
  OR @sortOrder NOT IN (''name'', ''created_date'')
BEGIN
  RAISERROR(''Invalid params'', 11, 1);
  RETURN;
END

DECLARE @sql NVARCHAR(MAX) = N''SELECT column_list_please
  FROM dbo.Product ORDER BY '' + @sortOrder + '' '' + @sortDir;

EXEC sp_executesql @sql;

Otra ventaja para el SQL dinámico, a pesar de todo el miedo que se difunde al respecto: puede obtener el mejor plan para cada variación de clasificación, en lugar de un único plan que se optimizará para cualquier variación de clasificación que use primero. También funcionó mejor universalmente en una comparación de rendimiento reciente que ejecuté:

http://sqlperformance.com/conditional-order-by

Answer 4

declare @str varchar(max)
set @str = ''select * from Product order by '' + @sortOrder + '' '' + @sortDir
exec(@str)