My stored procedure receives a parameter which is a comma-separated string:
我的存储过程接收一个参数,它是一个逗号分隔的字符串:
DECLARE @Account AS VARCHAR(200)
SET @Account = 'SA,A'
I need to make from it this statement:
我需要从它说:
WHERE Account IN ('SA', 'A')
What is the best practice for doing this?
这样做的最佳实践是什么?
3 个解决方案
#1
30
Create this function (sqlserver 2005+)
创建这个函数(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 this statement
使用这个语句
SELECT *
FROM yourtable
WHERE account in (SELECT val FROM dbo.f_split(@account, ','))
Comparing my split function to XML split:
比较我的分割函数和XML分割:
Testdata:
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:
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:
f_split:
select count(*) from a cross apply clausens_base.dbo.f_split(a.txt, ',')
Elapsed time: 43 seconds
This will change from run to run, but you get the idea
这将从运行变为运行,但是您应该明白了
#2
3
Try this one -
试试这个,
DDL:
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')
Query:
查询:
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)
))
Output:
输出:
Extended statistics:
扩展的统计信息:
SSMS SET STATISTICS TIME + IO:
SSMS设置统计时间+ IO:
XML:
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:
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.
#3
2
The most efficient way is to use CLR function for split string. See this article for examples and performance comparison
最有效的方法是将CLR函数用于分割字符串。有关示例和性能比较,请参阅本文
#1
30
Create this function (sqlserver 2005+)
创建这个函数(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 this statement
使用这个语句
SELECT *
FROM yourtable
WHERE account in (SELECT val FROM dbo.f_split(@account, ','))
Comparing my split function to XML split:
比较我的分割函数和XML分割:
Testdata:
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:
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:
f_split:
select count(*) from a cross apply clausens_base.dbo.f_split(a.txt, ',')
Elapsed time: 43 seconds
This will change from run to run, but you get the idea
这将从运行变为运行,但是您应该明白了
#2
3
Try this one -
试试这个,
DDL:
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')
Query:
查询:
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)
))
Output:
输出:
Extended statistics:
扩展的统计信息:
SSMS SET STATISTICS TIME + IO:
SSMS设置统计时间+ IO:
XML:
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:
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.
#3
2
The most efficient way is to use CLR function for split string. See this article for examples and performance comparison
最有效的方法是将CLR函数用于分割字符串。有关示例和性能比较,请参阅本文