Analisar uma string separada por vírgula para criar uma lista de strings na cláusula Where

Meu procedimento armazenado recebe um parâmetro que é uma string separada por vírgula:

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

Eu preciso fazer com que esta declaração:

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

Qual é a melhor prática para fazer isso?

Crie esta function (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 

use esta declaração

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

Comparando minha function split à divisão XML:

Dados de teste:

 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(tcvalue('.', 'VARCHAR(20)')) FROM ( SELECT top 100000 x = CAST('' + REPLACE(txt, ',', '') + '' 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 

Isso vai mudar de corrida para corrida, mas você tem a ideia

Tente este –

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') 

Inquerir:

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

Saída:

proff

Estatísticas estendidas:

stat

SSMS SET STATISTICS TIME + 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. 

A maneira mais eficiente é usar a function CLR para string dividida. Veja este artigo para exemplos e comparação de desempenho