Ordre conditionnel T-SQL par
J'essaie d'écrire une procédure stockée qui renvoie une liste d'objets avec l'ordre de tri et la direction de tri sélectionnés par l'utilisateur et transmis en tant que paramètres sql.
Disons que j'ai une table de produits avec les colonnes suivantes: product_id (int), name (varchar), value (int), created_date(datetime) et paramètres @sortDir et @sortOrder
Je veux faire quelque chose comme
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
J'ai essayé de le faire avec des instructions case mais j'avais des problèmes puisque les types de données étaient différent. Quelqu'un a des suggestions?
4 réponses
CASE est une expression qui renvoie une valeur. Ce n'est pas pour le contrôle du flux, comme IF. Et vous ne pouvez pas utiliser IF dans une requête.
Malheureusement, il y a certaines limitations avec les expressions CASE qui rendent difficile de faire ce que vous voulez. Par exemple, toutes les branches d'une expression CASE doivent renvoyer le même type, OU être implicitement convertibles au même type. Je n'essaierais pas ça avec des cordes et des dates. Vous ne pouvez pas non plus utiliser CASE pour spécifier le tri direction.
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;
Une solution sans doute plus facile (surtout si cela devient plus complexe) consiste à utiliser dynamic SQL. Pour contrecarrer L'injection SQL, vous pouvez tester les valeurs:
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;
Un autre avantage pour le SQL dynamique, malgré toute la peur qui s'y répand: vous pouvez obtenir le meilleur plan pour chaque variation de tri, au lieu d'un seul plan qui optimisera la variation de tri que vous avez utilisée en premier. Il a également obtenu les meilleurs résultats universellement dans une comparaison de performance récente I ran:
Vous avez besoin d'une instruction case, bien que j'utiliserais plusieurs instructions case:
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
Avoir quatre clauses différentes élimine le problème de la conversion entre les types.
Il y a plusieurs façons de le faire. Une façon serait:
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
Vous pouvez également le faire en utilisant dynamic sql.
declare @str varchar(max)
set @str = 'select * from Product order by ' + @sortOrder + ' ' + @sortDir
exec(@str)