Comment puis-je utiliser des parameters facultatifs dans une procédure stockée T-SQL?

Je crée une procédure stockée pour effectuer une recherche dans une table. J’ai beaucoup de champs de recherche différents, tous facultatifs. Est-il possible de créer une procédure stockée qui gérera cela? Disons que j’ai une table avec quatre champs: ID, Prénom, Nom et Titre. Je pourrais faire quelque chose comme ça:

CREATE PROCEDURE spDoSearch @FirstName varchar(25) = null, @LastName varchar(25) = null, @Title varchar(25) = null AS BEGIN SELECT ID, FirstName, LastName, Title FROM tblUsers WHERE FirstName = ISNULL(@FirstName, FirstName) AND LastName = ISNULL(@LastName, LastName) AND Title = ISNULL(@Title, Title) END 

Ce genre de travaux. Cependant, il ignore les enregistrements où FirstName, LastName ou Title sont NULL. Si Title n’est pas spécifié dans les parameters de recherche, je souhaite inclure des enregistrements dont Title est NULL – idem FirstName et LastName. Je sais que je pourrais probablement le faire avec SQL dynamic mais je voudrais éviter cela.

Changer dynamicment les recherches en fonction des parameters donnés est un sujet compliqué et le faire d’une manière ou d’une autre, même avec une très légère différence, peut avoir des conséquences énormes sur les performances. La clé consiste à utiliser un index, à ignorer le code compact, à ignorer la répétition du code, à établir un bon plan d’exécution des requêtes (utilisez un index).

Lisez ceci et considérez toutes les méthodes. Votre meilleure méthode dépendra de vos parameters, de vos données, de votre schéma et de votre utilisation réelle:

Conditions de recherche dynamic dans T-SQL par by Erland Sommarskog

La malédiction et les bénédictions de SQL dynamic par Erland Sommarskog

Si vous disposez de la version appropriée de SQL Server 2008 (SQL 2008 SP1 CU5 (10.0.2746) et versions ultérieures), vous pouvez utiliser cette astuce pour utiliser réellement un index:

Ajoutez OPTION (RECOMPILE) à votre requête, voir l’article d’Erland et SQL Server résoudra l’ OR de l’intérieur (@LastName IS NULL OR LastName= @LastName) avant la création du plan de requête en fonction des valeurs d’exécution des variables locales et un index peut être utilisé.

Cela fonctionnera pour toute version de SQL Server (renvoyer les résultats appropriés), mais incluez uniquement l’OPTION (RECOMPILE) si vous êtes sur SQL 2008 SP1 CU5 (10.0.2746) et versions ultérieures. L’OPTION (RECOMPILE) recomstackra votre requête, seule la version listée la recomstackra en fonction des valeurs d’exécution actuelles des variables locales, ce qui vous donnera les meilleures performances. Si ce n’est pas sur cette version de SQL Server 2008, laissez simplement cette ligne.

 CREATE PROCEDURE spDoSearch @FirstName varchar(25) = null, @LastName varchar(25) = null, @Title varchar(25) = null AS BEGIN SELECT ID, FirstName, LastName, Title FROM tblUsers WHERE (@FirstName IS NULL OR (FirstName = @FirstName)) AND (@LastName IS NULL OR (LastName = @LastName )) AND (@Title IS NULL OR (Title = @Title )) OPTION (RECOMPILE) ---<<< 

La réponse de @KM est bonne dans la mesure où elle va bien, mais elle ne parvient pas à donner suite à l’un de ses premiers conseils.

…, ignorez le code compact, ignorez le souci de répéter le code, …

Si vous souhaitez obtenir les meilleures performances, vous devez écrire une requête sur mesure pour chaque combinaison possible de critères facultatifs. Cela peut sembler extrême, et si vous avez beaucoup de critères optionnels, cela peut être le cas, mais la performance est souvent un compromis entre les efforts et les résultats. En pratique, il peut exister un ensemble commun de combinaisons de parameters pouvant être ciblées avec des requêtes personnalisées, puis une requête générique (selon les autres réponses) pour toutes les autres combinaisons.

 CREATE PROCEDURE spDoSearch @FirstName varchar(25) = null, @LastName varchar(25) = null, @Title varchar(25) = null AS BEGIN IF (@FirstName IS NOT NULL AND @LastName IS NULL AND @Title IS NULL) -- Search by first name only SELECT ID, FirstName, LastName, Title FROM tblUsers WHERE FirstName = @FirstName ELSE IF (@FirstName IS NULL AND @LastName IS NOT NULL AND @Title IS NULL) -- Search by last name only SELECT ID, FirstName, LastName, Title FROM tblUsers WHERE LastName = @LastName ELSE IF (@FirstName IS NULL AND @LastName IS NULL AND @Title IS NOT NULL) -- Search by title only SELECT ID, FirstName, LastName, Title FROM tblUsers WHERE Title = @Title ELSE IF (@FirstName IS NOT NULL AND @LastName IS NOT NULL AND @Title IS NULL) -- Search by first and last name SELECT ID, FirstName, LastName, Title FROM tblUsers WHERE FirstName = @FirstName AND LastName = @LastName ELSE -- Search by any other combination SELECT ID, FirstName, LastName, Title FROM tblUsers WHERE (@FirstName IS NULL OR (FirstName = @FirstName)) AND (@LastName IS NULL OR (LastName = @LastName )) AND (@Title IS NULL OR (Title = @Title )) END 

L’avantage de cette approche est que, dans les cas courants traités par des requêtes sur mesure, la requête est aussi efficace que possible: les critères non appliqués n’ont aucun impact. De plus, les index et autres améliorations de performances peuvent cibler des requêtes spécifiques au lieu d’essayer de satisfaire toutes les situations possibles.

Vous pouvez faire dans le cas suivant,

 CREATE PROCEDURE spDoSearch @FirstName varchar(25) = null, @LastName varchar(25) = null, @Title varchar(25) = null AS BEGIN SELECT ID, FirstName, LastName, Title FROM tblUsers WHERE (@FirstName IS NULL OR FirstName = @FirstName) AND (@LastNameName IS NULL OR LastName = @LastName) AND (@Title IS NULL OR Title = @Title) END 

Cependant, dépendre des données peut parfois mieux créer une requête dynamic et les exécuter.

Étendez votre condition WHERE :

 WHERE (FirstName = ISNULL(@FirstName, FirstName) OR COALESCE(@FirstName, FirstName, '') = '') AND (LastName = ISNULL(@LastName, LastName) OR COALESCE(@LastName, LastName, '') = '') AND (Title = ISNULL(@Title, Title) OR COALESCE(@Title, Title, '') = '') 

c’est-à-dire combiner différents cas avec des conditions booléennes.

Cinq ans de retard à la fête.

Il est mentionné dans les liens fournis de la réponse acceptée, mais je pense qu’il mérite une réponse explicite sur SO – en construisant dynamicment la requête en fonction des parameters fournis. Par exemple:

Installer

 -- drop table Person create table Person ( PersonId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_Person PRIMARY KEY, FirstName NVARCHAR(64) NOT NULL, LastName NVARCHAR(64) NOT NULL, Title NVARCHAR(64) NULL ) GO INSERT INTO Person (FirstName, LastName, Title) VALUES ('Dick', 'Ormsby', 'Mr'), ('Serena', 'Kroeger', 'Ms'), ('Marina', 'Losoya', 'Mrs'), ('Shakita', 'Grate', 'Ms'), ('Bethann', 'Zellner', 'Ms'), ('Dexter', 'Shaw', 'Mr'), ('Zona', 'Halligan', 'Ms'), ('Fiona', 'Cassity', 'Ms'), ('Sherron', 'Janowski', 'Ms'), ('Melinda', 'Cormier', 'Ms') GO 

Procédure

 ALTER PROCEDURE spDoSearch @FirstName varchar(64) = null, @LastName varchar(64) = null, @Title varchar(64) = null, @TopCount INT = 100 AS BEGIN DECLARE @SQL NVARCHAR(4000) = ' SELECT TOP ' + CAST(@TopCount AS VARCHAR) + ' * FROM Person WHERE 1 = 1' PRINT @SQL IF (@FirstName IS NOT NULL) SET @SQL = @SQL + ' AND FirstName = @FirstName' IF (@LastName IS NOT NULL) SET @SQL = @SQL + ' AND FirstName = @LastName' IF (@Title IS NOT NULL) SET @SQL = @SQL + ' AND Title = @Title' EXEC sp_executesql @SQL, N'@TopCount INT, @FirstName varchar(25), @LastName varchar(25), @Title varchar(64)', @TopCount, @FirstName, @LastName, @Title END GO 

Usage

 exec spDoSearch @TopCount = 3 exec spDoSearch @FirstName = 'Dick' 

Avantages:

  • facile à écrire et à comprendre
  • flexibilité – générez facilement la requête pour des filtrages plus complexes (par exemple, TOP dynamic)

Les inconvénients:

  • Problèmes de performances possibles en fonction des parameters, des index et du volume de données fournis

Pas de réponse directe, mais liée au problème aka la grande image

Généralement, ces procédures stockées de filtrage ne flottent pas, mais sont appelées depuis une couche de service. Cela laisse la possibilité de déplacer la logique métier (filtrage) de la couche SQL vers la couche service.

Un exemple utilise LINQ2SQL pour générer la requête basée sur les filtres fournis:

  public IList GetServiceModels(CustomFilter filters) { var query = DataAccess.SomeRepository.AllNoTracking; // partial and insensitive search if (!ssortingng.IsNullOrWhiteSpace(filters.SomeName)) query = query.Where(item => item.SomeName.IndexOf(filters.SomeName, SsortingngComparison.OrdinalIgnoreCase) != -1); // filter by multiple selection if ((filters.CreatedByList?.Count ?? 0) > 0) query = query.Where(item => filters.CreatedByList.Contains(item.CreatedById)); if (filters.EnabledOnly) query = query.Where(item => item.IsEnabled); var modelList = query.ToList(); var serviceModelList = MappingService.MapEx(modelList); return serviceModelList; } 

Avantages:

  • requête générée dynamicment basée sur les filtres fournis. Aucun reniflage de paramètre ou de recompilation requirejs
  • un peu plus facile à écrire pour ceux du monde de la POO
  • En général, les performances sont optimales, car des requêtes “simples” seront émises (des index appropriés sont néanmoins nécessaires)

Les inconvénients:

  • Les limitations LINQ2QL peuvent être atteintes et forcer une rétrogradation vers LINQ2Objects ou revenir à la solution SQL pure selon le cas
  • l’écriture négligente de LINQ peut générer des requêtes affreuses (ou de nombreuses requêtes, si les propriétés de navigation sont chargées)

Cela fonctionne aussi:

  ... WHERE (FirstName IS NULL OR FirstName = ISNULL(@FirstName, FirstName)) AND (LastName IS NULL OR LastName = ISNULL(@LastName, LastName)) AND (Title IS NULL OR Title = ISNULL(@Title, Title))