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?
Add a comment
|
4 Answers
Create this 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 this statement
SELECT *
FROM yourtable
WHERE account in (SELECT val FROM dbo.f_split(@account, ','))
Comparing my split function to 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
This will change from run to run, but you get the idea
3 Comments
Surya Pratap
what will happen if
@param is null, I think set @param += @delimiter should be set @param =ISNULL(@param,'')+@delimiter
t-clausen.dk
@SuryaPratap plain and simple - no. That would make no difference for the result.
R Loomas
How do I change this if the input list of comma separated values are integers and the "WHERE projectID in (1,2,3,4,5,6)"? Do I cast the "val" in the call to the function?
Try this one -
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:
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.
12 Comments
Devart
Try do this:
WHERE [Vertical] = @Vertical AND (@Account IS NULL OR Account IN (...))
user2514925
Thank you for the Response I've tried it but 2 records should be returned as a result here 0 record only returning
user2514925
[EmpId], [FirstName], [LastName], [Domain], [Vertical], [Account] 345, 'Priya', 'Palanisamy', 'DotNet', 'LS', 'Abbott' 346, 'Kavitha', 'Amirtharaj', 'DotNet', 'CG', 'Diageo' 647, 'Kala', 'Haribabu', 'DotNet', 'DotNet', 'IMS' i want to get the result of "select * from Table where vertical='DotNet' and Account in ('CG','LS')" this query using Storedprocedure(by passing Vertical,Account Values Dynamically)
Devart
What is contained in the the variables
@Vertical, @Account?t-clausen.dk
@Devart results included in my answer. B.t.w. my answer is also slightly faster than the CTE in Alexanders link
|
The most efficient way is to use CLR function for split string. See this article for examples and performance comparison
1 Comment
t-clausen.dk
the CTE function described in that article is really weak(compared to the one i described). He selecting all the values inside in CTE instead of finding the positions of the delimiter. This is at the cost of alot more computer ressources than necessary and not really giving the CTE a fair chance.
SQL server 2016+ offers String_Split described here: https://www.sqlshack.com/the-string-split-function-in-sql-server/, which worked for my purpose well and is easy to use, for example like this:
declare @InClauesQueryString nvarchar(100) = '1,2';
declare @exampleTable table(ID int,Val nvarchar(100));
INSERT INTO @exampleTable (ID, Val) VALUES (1,'test1');
INSERT INTO @exampleTable (ID, Val) VALUES (2,'test2');
select * from @exampleTable where id in (select * from string_split(@InClauesQueryString,','));
Comments
