Dynamique Tri dans les Procédures Stockées SQL
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 moderne SGBDR des 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 Procédure Stockée des 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
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 bon 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, et tout simplement 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 ne 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: soyez heureux, je n'ai pas montré mes 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() de PLUS, 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 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 de 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. 🙂
- Reportez-vous à stackoverflow.com/questions/3659981/... trop -- SQL Server ORDRE dynamique PAR avec un mélange de types de données
- SQL dynamique est de LOIN supérieure de la sorte... SI [et c'est un grand SI] ..votre Couche d'Accès aux Données est stricte et votre dynamique SQL est généré par un système qui est rigidement programmé avec les SGBDR règles exprimées dans une forme parfaite. Un Algorithmiquement Base de données d'Ingénierie de l'Architecture est une chose de la beauté...
Vous devez vous connecter pour publier un commentaire.
Oui, c'est une douleur, et la façon dont vous êtes en train de faire, il ressemble à ce que je fais:
Pour moi, ceci est beaucoup mieux encore que la construction de SQL dynamique de code, qui se transforme en une évolutivité et la maintenance cauchemar pour les Administrateurs de base de données.
Ce que je fais de code est refactoriser la pagination et le tri donc, j'ai au moins de ne pas avoir beaucoup de répétitions il avec le remplissage de valeurs pour
@SortExpr
et@SortDir
.Aussi loin que la vue SQL, garder la conception et la mise en forme de la même entre les différentes procédures stockées, il est donc au moins propre et reconnaissable, quand vous allez faire des changements.
Cette approche maintient les colonnes triables d'être dupliqué deux fois dans l'ordre, et est un peu plus lisible de l'OMI:
SQL dynamique est toujours une option. Vous avez juste à décider si cette option n'est plus acceptable que ce que vous avez actuellement.
Voici un article qui montre que: http://www.4guysfromrolla.com/webtech/010704-1.shtml.
Mes applications le font beaucoup, mais ils sont tous de façon dynamique la construction du SQL. Cependant, lorsque j'ai affaire avec des procédures stockées-je faire ceci:
select * from dbo.fn_myData() where ... order by ...
de sorte que vous pouvez dynamiquement spécifier l'ordre de tri n'.Puis au moins la partie dynamique est dans votre application, mais la base de données est encore en train de faire le levage lourd.
Une procédure stockée technique (hack?) J'ai utilisé pour éviter le SQL dynamique pour certains emplois est d'avoir une unique colonne de tri. I. e.,
C'est facile à battre dans la soumission -- vous pouvez concat champs dans votre mySort colonne, d'inverser l'ordre avec les mathématiques ou les fonctions de date, etc.
De préférence, si, je utiliser mon asp.net gridviews ou d'autres objets avec construire-dans le tri à faire le tri pour moi APRÈS l'extraction des données bof Sql-Server. Ou même si elle n'est pas intégrée -- par exemple, les tables de données, etc. dans asp.net.
Il y a un couple de façons différentes que vous pouvez pirater ce dans.
Conditions préalables:
sp
un défaut)
Ensuite l'insérer dans une table temporaire:
Méthode n ° 2: mettre en place un serveur lié à lui-même, puis sélectionnez à partir de cette aide openquery:
http://www.sommarskog.se/share_data.html#OPENQUERY
Il peut y avoir une troisième option, depuis votre serveur a beaucoup de cycles de rechange - utiliser une aide de la procédure pour faire le tri via une table temporaire. Quelque chose comme
Mise en garde: je n'ai pas testé, mais il "devrait" dans SQL Server 2005 (ce qui permettra de créer une table temporaire à partir d'un ensemble de résultats sans spécifier les colonnes à l'avance.)
À un certain point, n'est-il pas devenu la peine de se déplacer loin de procédures stockées et il suffit d'utiliser des requêtes paramétrées pour éviter ce genre de hackery?
Je suis d'accord, l'utilisation côté client. Mais il semble que n'est pas la réponse que vous souhaitez entendre.
Donc, elle est parfaite comme elle est. Je ne sais pas pourquoi vous voulez le modifier, ou même demander "Est-il une meilleure façon." Vraiment, il devrait être appelé "Le Chemin". En outre, il semble fonctionner et de s'adapter aux besoins du projet est très bien et sera probablement suffisamment extensible pour les années à venir. Depuis vos bases de données ne sont pas imposables et que le tri est vraiment vraiment facile il devrait le rester pour les années à venir.
Je ne vous tracassez pas.
Lorsque vous êtes pagination triés résultats, SQL dynamique est une bonne option. Si vous êtes paranoïaque à propos de l'injection SQL, vous pouvez utiliser les numéros de colonne à la place du nom de la colonne. Je l'ai fait avant d'utiliser des valeurs négatives pour la descente. Quelque chose comme ceci...
Ensuite vous avez juste besoin de s'assurer que le numéro est à l'intérieur de 1 au nombre de colonnes. Vous pouvez même étendre ce à une liste de numéros de colonne et de les analyser dans un tableau d'entiers à l'aide d'une fonction comme cette. Ensuite, vous de construire la clause order by comme si...
Seul inconvénient est que vous devez vous rappeler l'ordre de chaque colonne sur le côté client. En particulier, lorsque vous n'avez pas afficher toutes les colonnes, ou vous les afficher dans un ordre différent. Lorsque le client veut de tri, vous devez mapper les noms de colonne de la colonne de l'ordre et de la génération de la liste d'entiers.
Un argument à l'encontre de faire le tri, côté client, de gros volumes de données et la pagination. Une fois votre nombre de lignes obtient au-delà de ce que vous pouvez facilement afficher, vous êtes souvent le tri dans le cadre d'un saut/prendre, ce qui, vous voudrez probablement pour s'exécuter dans SQL.
Pour Entity Framework, vous pouvez utiliser une procédure stockée pour gérer votre texte de recherche. Si vous rencontrez le même problème, la solution que j'ai vu, c'est d'utiliser une procédure stockée pour la recherche, en ne retournant que l'id d'un jeu de clés pour le match. Ensuite, re-requête (avec le tri) à l'encontre de la db à l'aide de l'id dans une liste (contient). EF gère assez bien, même lorsque l'IDENTIFIANT est assez grande. Oui, c'est deux allers-retours, mais il vous permet de toujours garder votre tri dans la base de données, qui peut être important dans certaines situations, et vous empêche d'écrire une cargaison de logique dans la procédure stockée.
Désolé je suis en retard à la fête, mais voici une autre option pour ceux qui veulent vraiment éviter de SQL dynamique, mais qui veulent la flexibilité qu'il offre:
Au lieu de générer dynamiquement le SQL à la volée, écrire du code pour générer un unique proc pour chaque variation possible. Ensuite, vous pouvez écrire une méthode dans le code pour regarder les options de la recherche et de l'avoir du choix de la proc d'appel.
Si vous n'avez que peu de variations, alors vous pouvez simplement créer le procs par la main. Mais si vous avez beaucoup de variations, alors au lieu d'avoir à les maintenir tous les, vous de maintenir votre proc générateur au lieu de l'avoir à les recréer.
Comme un avantage supplémentaire, vous obtiendrez de meilleurs plans SQL pour de meilleures performances de le faire de cette façon.
Comment la manipulation de tri sur les choses que l'affichage des résultats -- grilles, des rapports, etc. plutôt que sur SQL?
EDIT:
De clarifier les choses depuis cette réponse a vers le bas-voté plus tôt, je vais développer un peu...
Vous avez déclaré que vous connaissait côté client de tri mais il voulait orienter clairement de cela. C'est votre appel, bien sûr.
Ce que je veux souligner, cependant, est qu'en le faisant sur le côté client, vous êtes en mesure d'extraire les données une FOIS et ensuite, quand vous le voulez, par rapport à faire de multiples allers et retours au serveur chaque fois que le tri est changé.
Votre Serveur SQL n'est pas imposé dès maintenant et c'est génial. Il ne devrait pas l'être. Mais juste parce qu'il n'est pas surchargé mais ne veut pas dire que ça va rester comme ça pour toujours.
Si vous utilisez l'un de la plus récente ASP.NET des trucs pour affichage sur le web, beaucoup de ce genre de choses est déjà cuit droit en.
Est-il la peine de l'ajout de beaucoup de code à chaque procédure stockée juste pour le tri? Encore une fois, votre appel.
Je ne suis pas celui qui en fin de compte être en charge de la soutenir. Mais réfléchir à ce qui va être impliqué en tant que colonnes sont ajoutées/supprimées au sein de différents ensembles de données utilisés par les procédures stockées (nécessiter de modifications dans le CAS des déclarations) ou quand, soudain, au lieu de trier par deux colonnes, l'utilisateur décide qu'il est besoin de trois -- vous obligeant à maintenant mettre à jour chacun de vos procédures stockées qui utilise cette méthode.
Pour moi, ça vaut le coup pour obtenir un travail côté client de la solution et l'appliquer à la poignée de l'utilisateur face à des affichages de données et être fait avec elle. Si une nouvelle colonne est ajoutée, c'est déjà traitées. Si l'utilisateur veut trier sur plusieurs colonnes, ils peuvent trier par deux ou vingt d'entre eux.
Cette solution peut ne fonctionner que dans .NET, je ne sais pas.
Je récupérer les données en C# avec l'ordre de tri initial dans le SQL clause order by, mettre les données dans un DataView, cache dans une variable de Session, et l'utiliser pour construire une page.
Lorsque l'utilisateur clique sur un titre de colonne pour trier (ou d'une page, ou un filtre), je ne reviendrai pas à la base de données. Au lieu de cela, je reviens à mon mis en cache DataView et de définir son "Sort" de la propriété à une expression que j'construire dynamiquement, comme je le SQL dynamique. ( Je ne le filtrage de la même façon, en utilisant le "RowFilter de la propriété").
Vous pouvez le voir/sentir travailler dans une démo de mon application, BugTracker.NET au http://ifdefined.com/btnet/bugs.aspx
Vous devriez éviter de SQL Server tri, sauf si nécessaire. Pourquoi ne pas trier sur l'app serveur ou côté client? Aussi .NET Génériques ne exceptionnels sortin