C'est une question que j'ai passé des heures à la recherche dans le passé. Il me semble être quelque chose qui aurait dû être traitée par les SGBDR solutions, mais comme je n'ai pas encore trouvé quelque chose qui aborde vraiment ce que je vois être un incroyablement commune de la nécessité dans n'importe quel Web ou une application Windows avec une base de données back-end.
Je parle de dynamique de tri. Dans mon monde imaginaire, il devrait être aussi simple que quelque chose comme:
ORDER BY @sortCol1, @sortCol2
C'est l'exemple canonique donnée par newbie SQL et de la Procédure Stockée développeurs sur les forums à travers l'Internet. "Pourquoi n'est-ce pas possible?" demandent-ils. Invariablement, quelqu'un vient finalement le long de leur faire la leçon à propos de la compilation de la nature des procédures stockées, des plans d'exécution en général, et toutes sortes d'autres raisons pour lesquelles il n'est pas possible de mettre un paramètre directement dans un ORDER BY de la clause.
Je sais ce que certains d'entre vous sont déjà en train de penser: "Laisser le client faire le tri, alors." Naturellement, cette décharge le travail à partir de votre base de données. Dans notre cas, nos serveurs de base de données ne sont même pas casser une sueur 99% du temps, et ils ne sont même pas multi-core ou encore l'un de la multitude des améliorations à l'architecture de système qui se produisent tous les 6 mois. Pour cette seule raison, le fait d'avoir notre bases de données de la poignée de tri ne serait pas un problème. En outre, les bases de données sont très bonnes au tri. Ils sont optimisés pour elle et ont eu des années pour y arriver, la langue pour le faire est incroyablement flexible, plus intuitive et plus simple, et surtout de n'importe quel débutant SQL écrivain sait comment le faire, et plus important encore, ils savent comment le modifier, de faire des changements, faire de la maintenance, etc. Lors de vos bases de données sont loin d'être taxés et vous voulez juste pour simplifier (et de raccourcir!) le temps de développement, ce qui semble être un choix évident.
Puis il y a le web en question. J'ai joué avec JavaScript qui va le faire côté client de tri de tableaux en HTML, mais ils sont forcément ne sont pas assez flexibles pour mes besoins et, de nouveau, depuis mes bases de données ne sont pas trop taxés et peut faire le tri vraiment vraiment facilement, j'ai du mal à justifier le temps qu'il faudrait ré-écrire ou de roll-mon-propre JavaScript trieur. Le même va généralement pour le serveur-côté de tri, mais il est probablement déjà privilégiée par rapport à JavaScript. Je ne suis pas celui qui aime particulièrement la surcharge des jeux de données, afin de me poursuivre en justice.
Mais cela ramène au point qu'il n'est pas possible — ou plutôt, pas facilement. Je l'ai fait, avec l'accord préalable des systèmes, incroyablement hack façon d'avoir de la dynamique de tri. Elle n'était pas jolie, ni intuitif, simple ou flexible et un débutant SQL écrivain serait perdu en quelques secondes. Déjà ce qui est à la recherche non pas d'une "solution", mais une "complication."
Les exemples suivants ne sont pas destinés à exposer à toute sorte de pratiques exemplaires ou bon style de codage ou de quoi que ce soit, ni qu'ils sont indicatifs de mes capacités en tant que T-SQL programmeur. Ils sont ce qu'ils sont et je suis entièrement d'admettre qu'ils sont source de confusion, mauvaise forme, ou tout simplement de hack.
Nous passons une valeur entière en tant que paramètre à une procédure stockée (appelons le paramètre juste "tri") et de déterminer un tas d'autres variables. Par exemple, disons-le, de tri est de 1 (ou par défaut):
DECLARE @sortCol1 AS varchar(20)
DECLARE @sortCol2 AS varchar(20)
DECLARE @dir1 AS varchar(20)
DECLARE @dir2 AS varchar(20)
DECLARE @col1 AS varchar(20)
DECLARE @col2 AS varchar(20)
SET @col1 = 'storagedatetime';
SET @col2 = 'vehicleid';
IF @sort = 1 -- Default sort.
BEGIN
SET @sortCol1 = @col1;
SET @dir1 = 'asc';
SET @sortCol2 = @col2;
SET @dir2 = 'asc';
END
ELSE IF @sort = 2 -- Reversed order default sort.
BEGIN
SET @sortCol1 = @col1;
SET @dir1 = 'desc';
SET @sortCol2 = @col2;
SET @dir2 = 'desc';
END
Vous pouvez déjà voir comment, si je l'ai déclaré plus d' @colX variables pour définir les autres colonnes que je pouvais vraiment faire preuve de créativité avec les colonnes à trier en fonction de la valeur de "trier"... pour l'utiliser, il finit généralement à la recherche comme suit incroyablement bordélique clause:
ORDER BY
CASE @dir1
WHEN 'desc' THEN
CASE @sortCol1
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END DESC,
CASE @dir1
WHEN 'asc' THEN
CASE @sortCol1
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END,
CASE @dir2
WHEN 'desc' THEN
CASE @sortCol2
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END DESC,
CASE @dir2
WHEN 'asc' THEN
CASE @sortCol2
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END
Évidemment, c'est un très dépouillé exemple. Le vrai truc, depuis que nous avons habituellement quatre ou cinq colonnes à l'appui, le tri, chaque avec de possibles secondaire ou même une troisième colonne de tri, en plus de cela (par exemple, date décroissante puis triés, puis par nom croissant) et chaque appui bi-directionnelle de tri qui a pour effet de doubler le nombre de cas. Ouais... ça devient poilu vraiment rapide.
L'idée est que l'on peut "facilement" modifier le tri des cas tels que vehicleid soit trié avant la storagedatetime... mais le pseudo-flexibilité, au moins dans cet exemple simple, vraiment s'arrête là. Essentiellement, chaque cas qui échoue à un test (parce que notre méthode de tri ne s'applique pas pour cette fois autour) rend une valeur NULL. Et donc vous vous retrouvez avec une clause qui fonctionne comme suit:
ORDER BY NULL DESC, NULL, [storagedatetime] DESC, blah blah
Vous obtenez l'idée. Cela fonctionne parce que SQL Server ne tient pas compte des valeurs nulles dans les clauses order by. C'est incroyablement difficile à maintenir, comme n'importe qui avec un travail de base de connaissances de SQL pouvez probablement voir. Si j'ai perdu l'un de vous, ne vous sentez pas mal. Il nous a fallu beaucoup de temps pour le faire fonctionner et nous avons encore à se confondre en essayant de le modifier ou d'en créer de nouveaux, comme elle. Heureusement, il n'a pas besoin de changer souvent, sinon il deviendrait rapidement "n'en vaut pas la peine."
Pourtant, il n' travail.
Ma question est donc: est-il un meilleur moyen?
Je suis d'accord avec d'autres solutions que la Procédure Stockée, car je me rends compte qu'il n'est peut-être pas le chemin à parcourir. De préférence, j'aimerais savoir si quelqu'un peut faire mieux dans la Procédure Stockée, mais si non, comment avez-vous toutes poignée permettant à l'utilisateur de manière dynamique les tables de tri de données (bi-directionnelle, trop) ASP.NET?
Et merci pour la lecture (ou de parcourir au moins) une longue question!
PS: je suis content de ne pas montrer mon exemple d'une procédure stockée qui prend en charge dynamique de tri, de filtrage dynamique/texte-à la recherche de colonnes, pagination via ROWNUMBER() DESSUS, ET try...catch transaction avec le retour arrière sur les erreurs... "monstre de la taille d'" ne même pas commencer à les décrire.
Mise à jour:
- Je voudrais éviter de SQL dynamique. L'analyse d'une chaîne et de l'exécution d'un EXEC sur elle défaites beaucoup de l'utilité d'avoir une procédure stockée dans la première place. Parfois, je me demande cependant si les cons de faire une telle chose ne serait pas la peine, au moins dans le cadre de ces dynamiques de tri des cas. Encore, je me sens toujours sale chaque fois que je fais du SQL dynamique des chaînes de caractères comme que — comme je suis encore en vie dans le Classique de l'ASP world.
- Beaucoup de la raison pour laquelle nous voulons des procédures stockées dans la première place est pour la sécurité. Je n'ai pas à faire l'appel sur des questions de sécurité, seulement de proposer des solutions. Avec SQL Server 2005, nous pouvons définir des autorisations (sur une base par utilisateur, si nécessaire), sur le niveau du schéma sur les différents procédures stockées, puis refuser des requêtes sur les tables directement. Critiquer les avantages et les inconvénients de cette approche est peut-être pour une autre question, mais encore une fois ce n'est pas ma décision. Je suis juste le code de singe. :)
