Procédure stockée T-SQL qui accepte plusieurs valeurs d'identité

Question

Existe-t-il une façon élégante de gérer le passage d'une liste d'identifiants comme paramètre à une procédure stockée ? Par exemple, je veux que les départements 1, 2, 5, 7, 20 soient retournés par ma procédure stockée. Dans le passé, j'ai passé une liste d'identifiants délimitée par des virgules, comme le code ci-dessous, mais je me sens vraiment sale en le faisant. SQL Server 2005 est ma seule limite applicable, je pense.

create procedure getDepartments
        @DepartmentIds varchar(max)
as
        declare @Sql varchar(max)

        select @Sql = 'select [Name] from Department where DepartmentId in (' + @DepartmentIds + ')'

        exec(@Sql)

Answer 1

Erland Sommarskog répond à cette question depuis 16 ans et fait autorité en la matière : Tableaux et listes en SQL Server .

Il existe au moins une douzaine de façons de transmettre un tableau ou une liste à une requête, chacune ayant ses propres avantages et inconvénients.

• Paramètres évalués par tableau . SQL 2008 et supérieur uniquement, et probablement la la plus proche d'une "meilleure" approche universelle.
• La méthode itérative . Passer une chaîne de caractères délimitée et la parcourir en boucle.
• Utilisation du CLR . SQL 2005 et supérieur à partir de langages .NET uniquement.
• XML . Très bon pour l'insertion de nombreuses lignes ; peut être excessif pour les SELECTs.
• Table des nombres . Performance/complexité plus élevée que la méthode itérative simple.
• Éléments à longueur fixe . La longueur fixe améliore la vitesse par rapport à la délimitée.
• Fonction des nombres . Variations de la table des nombres et Longueur fixe où les nombres sont générés dans une fonction plutôt que d'être plutôt qu'extraits d'une table.
• Expression récursive du tableau commun (CTE). SQL 2005 et supérieur, toujours pas trop complexe et plus performant que la méthode itérative.
• SQL dynamique . Peut être lent et a des implications en matière de sécurité.
• Passer la liste en tant que De nombreux paramètres . Fastidieux et sujet aux erreurs, mais simple.
• Méthodes vraiment lentes . Méthodes qui utilisent charindex, patindex ou LIKE.

Je ne peux vraiment pas recommander assez à lire l'article pour en savoir plus sur les compromis entre toutes ces options.

Answer 2

Oui, votre solution actuelle est sujette à des attaques par injection SQL.

La meilleure solution que j'ai trouvée est d'utiliser une fonction qui divise le texte en mots (il y en a quelques unes postées ici, ou vous pouvez utiliser celui de mon blog ), puis de le joindre à votre table. Quelque chose comme :

SELECT d.[Name]
FROM Department d
    JOIN dbo.SplitWords(@DepartmentIds) w ON w.Value = d.DepartmentId

Answer 3

Vous pourriez utiliser le XML.

Par exemple

declare @xmlstring as  varchar(100) 
set @xmlstring = '<args><arg value="42" /><arg2>-1</arg2></args>' 

declare @docid int 

exec sp_xml_preparedocument @docid output, @xmlstring

select  [id],parentid,nodetype,localname,[text]
from    openxml(@docid, '/args', 1)

La commande sp_xml_preparedocument (document préparé) est intégré.

Ceci produirait la sortie :

id  parentid    nodetype    localname   text
0   NULL    1   args    NULL
2   0   1   arg NULL
3   2   2   value   NULL
5   3   3   #text   42
4   0   1   arg2    NULL
6   4   3   #text   -1

qui a tout (plus ?) ce dont vous avez besoin.

Answer 4

Si vous avez l'intention de travailler souvent avec les valeurs, vous pouvez envisager de les écrire d'abord dans une table temporaire. Ensuite, il suffit de les joindre comme d'habitude.

De cette façon, vous n'effectuez qu'une seule analyse syntaxique.

Le plus simple est d'utiliser l'un des UDF 'Split', mais tant de personnes ont posté des exemples de ces UDF, que je me suis dit que j'allais prendre une autre voie ;)

Cet exemple créera une table temporaire sur laquelle vous pourrez vous joindre (#tmpDept) et la remplira avec les identifiants de département que vous avez fournis. Je suppose que vous les séparez par des virgules, mais vous pouvez - bien sûr - les modifier comme vous le souhaitez.

IF OBJECT_ID('tempdb..#tmpDept', 'U') IS NOT NULL
BEGIN
    DROP TABLE #tmpDept
END

SET @DepartmentIDs=REPLACE(@DepartmentIDs,' ','')

CREATE TABLE #tmpDept (DeptID INT)
DECLARE @DeptID INT
IF IsNumeric(@DepartmentIDs)=1
BEGIN
    SET @DeptID=@DepartmentIDs
    INSERT INTO #tmpDept (DeptID) SELECT @DeptID
END
ELSE
BEGIN
        WHILE CHARINDEX(',',@DepartmentIDs)>0
        BEGIN
            SET @DeptID=LEFT(@DepartmentIDs,CHARINDEX(',',@DepartmentIDs)-1)
            SET @DepartmentIDs=RIGHT(@DepartmentIDs,LEN(@DepartmentIDs)-CHARINDEX(',',@DepartmentIDs))
            INSERT INTO #tmpDept (DeptID) SELECT @DeptID
        END
END

Cela vous permettra de transmettre un seul identifiant de département, plusieurs identifiants séparés par des virgules, ou même plusieurs identifiants séparés par des virgules et des espaces.

Donc si vous avez fait quelque chose comme :

SELECT Dept.Name 
FROM Departments 
JOIN #tmpDept ON Departments.DepartmentID=#tmpDept.DeptID
ORDER BY Dept.Name

Vous verriez les noms de tous les ID de département que vous avez passés dans...

Encore une fois, cela peut être simplifié en utilisant une fonction pour remplir la table temporaire... Je l'ai principalement fait sans fonction, juste pour tuer l'ennui :-P

-- Kevin Fairchild

Answer 5

Une méthode XML super rapide, si vous souhaitez utiliser une procédure stockée et passer la liste des ID de département séparés par des virgules :

Declare @XMLList xml
SET @XMLList=cast('<i>'+replace(@DepartmentIDs,',','</i><i>')+'</i>' as xml)
SELECT x.i.value('.','varchar(5)') from @XMLList.nodes('i') x(i))

Tout le mérite revient à Guru Blog de Brad Schulz