Analyser chaîne séparée par des virgules de faire partie de la Liste des chaînes dans la clause where

Ma procédure stockée reçoit un paramètre qui est une chaîne séparée par des virgules:

DECLARE @Account AS VARCHAR(200)
SET @Account = 'SA,A'

J'ai besoin de faire cette déclaration: 

WHERE Account IN ('SA', 'A')

Quelle est la meilleure pratique pour le faire?

InformationsquelleAutor user2514925 | 2013-07-05
  1. 31

    De créer cette fonction (sqlserver 2005+)

    CREATE function [dbo].[f_split]
(
@param nvarchar(max), 
@delimiter char(1)
)
returns @t table (val nvarchar(max), seq int)
as
begin
set @param += @delimiter

;with a as
(
select cast(1 as bigint) f, charindex(@delimiter, @param) t, 1 seq
union all
select t + 1, charindex(@delimiter, @param, t + 1), seq + 1
from a
where charindex(@delimiter, @param, t + 1) > 0
)
insert @t
select substring(@param, f, t - f), seq from a
option (maxrecursion 0)
return
end

    utiliser cette instruction

    SELECT *
FROM yourtable 
WHERE account in (SELECT val FROM dbo.f_split(@account, ','))

    Comparant ma fonction de répartition pour XML split:

    Testdata:

    select top 100000 cast(a.number as varchar(10))+','+a.type +','+ cast(a.status as varchar(9))+','+cast(b.number as varchar(10))+','+b.type +','+ cast(b.status as varchar(9)) txt into a 
from master..spt_values a cross join master..spt_values b

    XML:

     SELECT count(t.c.value('.', 'VARCHAR(20)'))
 FROM (
     SELECT top 100000 x = CAST('<t>' + 
           REPLACE(txt, ',', '</t><t>') + '</t>' AS XML)
           from a
 ) a
 CROSS APPLY x.nodes('/t') t(c)

Elapsed time: 1:21 seconds

    f_split:

    select count(*) from a cross apply clausens_base.dbo.f_split(a.txt, ',')

Elapsed time: 43 seconds

    Cela va changer d'une exécution, mais vous obtenez l'idée

    • ce qui va se passer si @param est nul, je pense que l'ensemble @param += @delimiter doit être définie @param =ISNULL(@param,'')+@delimiter
    • la plaine et simple - pas. Que ne ferait aucune différence pour le résultat.
    InformationsquelleAutor t-clausen.dk
  2. 4

    Essayez celui -

    DDL:

    CREATE TABLE dbo.Table1 (
      [EmpId] INT
    , [FirstName] VARCHAR(7)
    , [LastName] VARCHAR(10)
    , [domain] VARCHAR(6)
    , [Vertical] VARCHAR(10)
    , [Account] VARCHAR(50)
    , [City] VARCHAR(50)
)

INSERT INTO dbo.Table1 ([EmpId], [FirstName], [LastName], [Vertical], [Account], [domain], [City])
VALUES 
     (345, 'Priya', 'Palanisamy', 'DotNet', 'LS', 'Abbott', 'Chennai'),
     (346, 'Kavitha', 'Amirtharaj', 'DotNet', 'CG', 'Diageo', 'Chennai'),
     (647, 'Kala', 'Haribabu', 'DotNet', 'DotNet', 'IMS', 'Chennai')

    Requête:

    DECLARE @Account VARCHAR(200)
SELECT @Account = 'CG,LS'

SELECT *
FROM Table1
WHERE [Vertical] = 'DotNet' AND (ISNULL(@Account, '') = '' OR Account IN (
     SELECT t.c.value('.', 'VARCHAR(20)')
     FROM (
         SELECT x = CAST('<t>' + 
               REPLACE(@Account, ',', '</t><t>') + '</t>' AS XML)
     ) a
     CROSS APPLY x.nodes('/t') t(c)
))

    De sortie:

    Analyser chaîne séparée par des virgules de faire partie de la Liste des chaînes dans la clause where

    Statistiques étendues:

    Analyser chaîne séparée par des virgules de faire partie de la Liste des chaînes dans la clause where

    SSMS ENSEMBLE DES STATISTIQUES EN TEMPS + IO:

    XML:

    (3720 row(s) affected)
Table 'temp'. Scan count 3, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 187 ms,  elapsed time = 242 ms.

    CTE:

    (3720 row(s) affected)
Table '#BF78F425'. Scan count 360, logical reads 360, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'temp'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 281 ms,  elapsed time = 335 ms.
    • Essayez de faire cela: WHERE [Vertical] = @Vertical AND (@Account IS NULL OR Account IN (...))
    • Merci pour la Réponse, j'ai essayé, mais 2 dossiers doivent être retournés comme un résultat de 0 enregistrer uniquement en cas de retour
    • [EmpId], [Prénom], [Nom], [Domain], [Vertical], [Compte] 345, 'Être', 'Palanisamy', 'DotNet', 'LS', 'Abbott' 346, 'Kavitha', 'Amirtharaj', 'DotNet', 'CG', 'Diageo' 647, "Kala", 'Haribabu', 'DotNet', 'DotNet', 'IMS' je veux obtenir le résultat de "select * from Table where vertical='DotNet" et Compte dans ('CG','LS')" cette requête à l'aide de Storedprocedure(en passant à la Verticale,les Valeurs de Compte Dynamiquement)
    • Ce qui est contenu dans les variables @Vertical, @Account?
    • contient vertical unique columnvalue et Compte contient séparation par virgule Columnvalues sélectionné de façon dynamique
    • laissez-nous continuer cette discussion dans le chat
    • Dans votre exemple, les données contenues dans une autre colonne. Veuillez consulter les mises à jour de réponse.
    • J'ai comparé les performances, ma solution est environ 20 % plus rapide
    • veuillez donner les résultats.
    • les résultats inclus dans ma réponse. B. t.w. ma réponse est également légèrement plus vite que le CTE dans Alexanders lien
    • veuillez consulter les mises à jour de réponse. Mon expérience, il n'y a pas d'approche unique pour cela. Essayez de comparer cette solution sur votre serveur.
    • Votre test est intéressant, j'ai essayé avec 1000 lignes et f_split était encore environ deux fois plus rapide. Avez-vous essayé plus d'une fois ?
    • démarrage à froid + serveur local sql2012 sp1 + effacer la mémoire cache après chaque exécution de + de 10 pistes.

    InformationsquelleAutor Devart
  3. 2

    Le moyen le plus efficace est d'utiliser la fonction CLR pour scinde une chaîne. Voir cet article pour des exemples et comparaison des performances

    • le CTE fonction décrite dans cet article est vraiment faible(par rapport à celle que j'ai décrite). Il sélectionnant toutes les valeurs à l'intérieur de CTE au lieu de trouver la position du séparateur. C'est au prix de beaucoup plus de ressources que nécessaire et de ne pas vraiment donner de la CTE une chance équitable.
    InformationsquelleAutor Alexander Sigachov