Dynamique de la commande de direction

J'ai écrit un SP qui accepte en tant que paramètres de colonne pour trier et de direction.

Je ne veux pas utiliser le SQL dynamique.

Le problème est avec le réglage du paramètre de direction.

C'est le partiel code:

SET @OrderByColumn = 'AddedDate'
SET @OrderDirection = 1;

…

ORDER BY 
    CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
        WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
        WHEN @OrderByColumn = 'AddedBy' THEN AddedBy
        WHEN @OrderByColumn = 'Title' THEN Title    
    END

Je recommande la cueillette de conversion de modèle pour AddedDate qui permet de s'assurer que les chaînes de caractères seront trier en ordre de date. Je crois que le défaut convertir peut changer avec la localité paramètres. Mieux définir explicitement et être en sécurité.
Il ne s'applique pas à certains autres types? Ou tout simplement la DATE?
Les numéros sont également un problème. Par exemple la conversion de 10, 1 et 2 varchar vous donnera une sorte de '1', '10', '2'. J'imagine que vous êtes la conversion Visible et AddedDate varchar en raison d'un CAS de retour tout de même type, correct?

InformationsquelleAutor markiz | 2009-07-18

31

Vous pourriez avoir deux quasi-identique ORDER BY éléments, ASC et un DESC, et d'étendre votre CASE déclaration à faire l'un ou l'autre d'entre eux toujours égale à une valeur unique:
```
ORDER BY
      CASE WHEN @OrderDirection = 0 THEN 1
      ELSE
           CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
                WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
                WHEN @OrderByColumn = 'AddedBy' THEN AddedBy
                WHEN @OrderByColumn = 'Title' THEN Title
           END
      END ASC,
      CASE WHEN @OrderDirection = 1 THEN 1
      ELSE
           CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
                WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
                WHEN @OrderByColumn = 'AddedBy' THEN AddedBy           
                WHEN @OrderByColumn = 'Title' THEN Title
           END
      END DESC
```
- pouvez vous s'il vous plaît répondre à la même question, j'ai demandé à Alex Noir en commentaire?
- Vous êtes en train de faire COMMANDER PAR Colonne1 ASC, Colonne2 DESC (le sens de la commande par Colonne1 croissant, puis par Colonne2 décroissant) mais au lieu de Colonne1 et Colonne2 vous avez une expression pour chacun.
- si AddedDate est une colonne datetime vous devez utiliser CONVERT(char(23),AddedDate,121) ou il ne sera pas trier correctement
- Dans MS SQL 2008 R2 j'ai trouvé-je obtenir un équivalent de ORDER BY 1 ASC, Title DESC, où 1 est la position de la colonne dans la liste de sélection, de sens dans ce cas, il serait de l'ordre par col[1] premier. Seriez-vous d'accord?
- Je ne crois pas, non. Il est exact que l'ORDRE de 1 commandes par la première colonne, mais je pense que cela ne fonctionne que lorsque la COMMANDE PAR l'argument est un nombre littéral. Si l'argument est une expression, alors le comportement est différent. Par exemple, ORDER BY 1+1 n' pas commander par la colonne 2. (En fait elle ne change pas l'ordre à tous, depuis le valeur de l'expression est le même pour toutes les lignes).
- J'ai été en mesure de le faire fonctionner à l'aide de CASE WHEN <condition> THEN NULL (j'ai eu une erreur sur la conversion de int le type de données nvarchar lors du tri par type nvarchar champ et en utilisant 1 au lieu de NULL. Il est intéressant de noter que vous ne pouvez pas utiliser NULL ou 1+1 normalement dans un ORDER BY. E. G. SELECT * FROM MyTable ORDER BY 1+1 ou NULL ou de tout autre constante de l'expression " provoque une erreur. Merci pour votre aide.
InformationsquelleAutor Gary McGill
12

Vous pouvez simplifier le CAS à l'aide de la fonction ROW_NUMBER qui trie vos données et permet de convertir en un pratique format de nombre entier. Surtout depuis que la question est balisé SQL Server 2005

Cela permet également de développer assez facilement pour traiter secondaire et tertiaire sortes

J'ai utilisé multiplicateur pour encore simplifier l'instruction select réelle et de réduire le risque de RBAR d'évaluation dans l'ORDRE PAR
```
DECLARE @multiplier int;

SELECT @multiplier = CASE @Direction WHEN 1 THEN -1 ELSE 1 END;

SELECT
     Columns you actually want
FROM
    (
    SELECT
         Columns you actually want,
         ROW_NUMBER() OVER (ORDER BY AddedDate) AS AddedDateSort,
         ROW_NUMBER() OVER (ORDER BY Visible) AS VisibleSort,
         ROW_NUMBER() OVER (ORDER BY AddedBy) AS AddedBySort,
         ROW_NUMBER() OVER (ORDER BY Title) AS TitleSort
    FROM
         myTable
    WHERE
         MyFilters...
    ) foo
ORDER BY
     CASE @OrderByColumn
        WHEN 'AddedDate' THEN AddedDateSort
        WHEN 'Visible' THEN VisibleSort    
        WHEN 'AddedBy' THEN AddedBySort
        WHEN 'Title' THEN TitleSort
     END * @multiplier;
```
- Avez-vous remarqué des performances avec la réalisation de plusieurs ROW_NUMBER()'s? Je comprends chacuns des données est différente.
- J'étais inquiet au sujet de la performance de la fonction row_number, après quelques tests de la fonction row_number mise en œuvre semble un peu mieux que les multiples cas de clause order by, sauf quand vous avez besoin d'une tranche de données. En effet, le meilleur résultat que j'ai constaté a été avec juste un rownumber sur le cas - voici un petit résumé avec mes requêtes.
InformationsquelleAutor gbn
4

Voici un exemple:
```
CREATE PROCEDURE GetProducts 
( 
    @OrderBy      VARCHAR(50), 
    @Input2       VARCHAR(30) 
) 
AS 
BEGIN 
    SET NOCOUNT ON 

    SELECT Id, ProductName, Description, Price, Quantity 
    FROM Products 
    WHERE ProductName LIKE @Input2 
    ORDER BY 
        CASE             
            WHEN @OrderBy = 'ProductNameAsc' THEN ProductName 
        END ASC, 
        CASE 
            WHEN @OrderBy = 'ProductNameDesc' THEN ProductName 
        END DESC 

END
```
À partir d'ici:

http://www.dominicpettifer.co.uk/Blog/21/dynamic-conditional-order-by-clause-in-sql-server-t-sql

Ascendant et Descendant les actions de
pour être regroupés dans une autre AFFAIRE
états, séparés par une virgule. Dans
votre code côté serveur/script assurez-vous que
pour ajouter 'Asc' ou 'Desc' sur le
commande par chaîne, ou vous pourriez avoir deux
Procédure stockée paramètres d'entrée pour
nom de la colonne et de l'ordre par la direction, si
vous voulez.
- pouvez-vous nous expliquer cette FIN ASC, (virgule) CAS de la syntaxe? Si l'entrée est ProductNameDesc, n'est-ce pas générer: .. ORDER BY ASC, ProductName DESC ? le "asc," à la suite de la fin est la partie de l'énoncé n'est-il pas? – markiz 0 secondes auparavant [supprimer ce commentaire]
- Je ne sais pas vraiment, je viens de googlé pour trouver cet article. Gary réponse ci-dessous semble utiliser la même technique.
- hey markiz, vous devriez juste essayer c'. Vous êtes essentiellement en disant: vous ne croyez pas que ce que cet article suggère de travail, si juste lui donner un aller, je parie qu'il fonctionne très bien.
- il fonctionne, mais je veux comprendre pourquoi.
- Sans doute la logique va quelque chose comme ceci: Chaque CAS est évalué, si aucun des cas où les déclarations dans le CAS de déclaration sont déclenchées puis l'ensemble de l'AFFAIRE instruction est ignorée, y compris la fuite de l'ASC.
- Alex: Si aucun des LORSQUE les conditions d'évaluer vrai et il n'y a pas d'AUTRE condition, alors la totalité de l'expression prend la valeur null. Depuis toutes les lignes aura la valeur null, la partie de la clause ORDER BY ne changera pas l'ordre. Si l'entrée est ProdcuctNameDesc les valeurs triées sur pour chaque ligne sera NULLE triés ASC et ProductName triés DESC & ProductNameASC serait trier par Nom du produit ASC, NULL Desc
- thx, qui fait plus de sens.
- merci Shannon
InformationsquelleAutor Alex Black

Cela fonctionne très bien pour moi– (where, order by, direction,Pagination)

parameters

@orderColumn  int ,
@orderDir  varchar(20),
@start  int ,
@limit  int


select * from items
WHERE        (items.status = 1) 
order by 

CASE WHEN @orderColumn = 0 AND @orderdir = 'desc' THEN items.[category] END DESC,    
CASE WHEN @orderColumn = 0 AND @orderdir = 'asc' THEN items.[category] END ASC,    
CASE WHEN @orderColumn = 1 AND @orderdir = 'desc' THEN items.[category] END DESC,
CASE WHEN @orderColumn = 1 AND @orderdir = 'asc' THEN items.[category] END ASC,
CASE WHEN @orderColumn = 2 AND @orderdir = 'desc' THEN items.[category] END DESC,
CASE WHEN @orderColumn = 2 AND @orderdir = 'asc' THEN items.[category] END ASC

OFFSET @start ROWS FETCH NEXT @limit ROWS ONLY

InformationsquelleAutor Arun Prasad E S

Plus compact version de accepté réponse, mais a accepté de répondre à cette fonctionne très bien seulement si le résultat des expressions après THEN ont le même type.

ORDER BY
    CASE @OrderDirection WHEN 0 THEN
        CASE @sortColumn
           WHEN 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
           WHEN 'Visible' THEN CONVERT(varchar(2), Visible)
           WHEN 'AddedBy' THEN AddedBy
           WHEN 'Title' THEN Title
        END
    END ASC,
    CASE @OrderDirection WHEN 1 THEN
        CASE @sortColumn
           WHEN 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
           WHEN 'Visible' THEN CONVERT(varchar(2), Visible)
           WHEN 'AddedBy' THEN AddedBy
           WHEN 'Title' THEN Title
        END
    END DESC

InformationsquelleAutor Vadim Ovchinnikov

Vous devez vous connecter pour publier un commentaire.