Introduction:

Lorsque l’on travaille avec SQL Server, il est courant de rencontrer des scénarios où nous avons besoin d’utiliser une variable pour un qualificateur de type IN dans une requête. Cela est particulièrement vrai lorsqu’il s’agit de chaînes délimitées par des virgules passées depuis une interface utilisateur. Malheureusement, SQL Server ne prend pas en charge l’utilisation directe d’une chaîne délimitée par des virgules dans une clause IN. Dans cet article, nous explorerons différentes approches pour gérer cette situation sans recourir à du SQL dynamique.

Utilisation d’une table:

Une façon de gérer le prédicat IN est de diviser la chaîne délimitée par des virgules en une table d’éléments individuels et d’utiliser cette table pour notre condition de qualification. Cela peut être réalisé en créant une table temporaire ou en utilisant une expression de table commune (CTE).

Jetons un coup d’œil à un exemple:

CREATE PROCEDURE SalesPersonSumByDates
( 
  @StartDate   DATETIME,
  @EndDate     DATETIME,
  @SalesPeople VARCHAR(50)
)
AS
-- Créer une table temporaire de personnes de vente.
-- Diviser la chaîne délimitée par des virgules en éléments individuels.
SELECT CONVERT(INT, Item) AS SalesPersonID
INTO #Sls
FROM dbo.DelimitedSplit8K(@SalesPeople, ',')

-- La requête pour le rapport.
SELECT  
  SO.SalesPersonID, C.FirstName + ' ' + C.LastName AS SalesPerson,
  COUNT(*) AS Orders,
  SUM(SO.SubTotal) AS TotalAmount
FROM Sales.SalesOrderHeader SO
INNER JOIN HumanResources.Employee E ON
  SO.SalesPersonID = E.EmployeeID
INNER JOIN Person.Contact C ON
  E.ContactID = C.ContactID
WHERE SO.OrderDate BETWEEN @StartDate And @EndDate
-- Le prédicat IN dynamique.
AND   SO.SalesPersonID IN (SELECT SalesPersonID FROM #Sls)
GROUP BY
  SO.SalesPersonID, C.FirstName, C.LastName

Dans cet exemple, nous divisons la chaîne délimitée par des virgules en éléments individuels en utilisant la fonction DelimitedSplit8K. Nous insérons ensuite ces éléments dans une table temporaire appelée #Sls. Enfin, nous utilisons la table #Sls dans le prédicat IN de notre requête pour filtrer les résultats en fonction des personnes de vente fournies.

Utilisation d’une CTE:

Si vous utilisez SQL Server 2005 ou une version ultérieure, vous pouvez vous passer de la création d’une table temporaire et utiliser une expression de table commune (CTE) à la place. Voici un exemple:

-- Utiliser une CTE à la place de la table.
; WITH CTESplit AS
( 
  SELECT CONVERT(INT, Item) AS SalesPersonID
  FROM dbo.DelimitedSplit8K(@SalesPeople, ',')
)
SELECT
  SO.SalesPersonID, C.FirstName + ' ' + C.LastName AS SalesPerson,
  COUNT(*) AS Orders,
  SUM(SO.SubTotal) AS TotalAmount
FROM Sales.SalesOrderHeader SO
INNER JOIN HumanResources.Employee E ON
  SO.SalesPersonID = E.EmployeeID
INNER JOIN Person.Contact C ON
  E.ContactID = C.ContactID
WHERE SO.OrderDate BETWEEN @StartDate And @EndDate
AND   SO.SalesPersonID IN (SELECT SalesPersonID FROM CTESplit)
GROUP BY
  SO.SalesPersonID, C.FirstName, C.LastName

Dans cet exemple, nous utilisons une CTE appelée CTESplit pour diviser la chaîne délimitée par des virgules en éléments individuels. Nous utilisons ensuite CTESplit dans le prédicat IN de notre requête pour filtrer les résultats en fonction des personnes de vente fournies.

Conclusion:

La gestion des prédicats IN dans SQL Server peut être réalisée en convertissant la chaîne délimitée par des virgules en une table ou en utilisant une CTE. Ce faisant, nous pouvons éviter le besoin de SQL dynamique et améliorer les performances de nos requêtes. Que vous choisissiez d’utiliser une table temporaire ou une CTE dépend de vos besoins spécifiques et de la version de SQL Server que vous utilisez.

Références:

– Article de Jeff Moden sur le passage de paramètres en tant que tableaux (presque) 1, 2 et 3 dimensions

– Article d’Alex Grinberg sur le tableau dans SQL Server 2000

– Article de Jeff Moden sur la fonction “CSV Splitter” SQL 8K améliorée

Données de test:

Pour les besoins de cet article, nous avons utilisé une version 2005 d’Adventureworks comme données de test. Cette base de données est largement disponible et peut être utilisée pour tester les requêtes mentionnées dans cet article.