Compose WHERE from comma separated values string

Transact-SQL
1

Hello,
I have to write a stored procedure that get a list of products from a parameter of codes like this:

@codes = AXOO1;AX005;AK013;

for example:

SELECT ProductName
FROM Products
WHERE ProductCode IN (@codes)

How can I accomplish this?

Thank you very much.

Luis

2

hi

hope this helps :slight_smile:

one way is by using the famous [de limited 8K splitter] function by Jeff Moden

please click arrow to the left for the 8K Function Code 
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;
go

select * from [DelimitedSplit8K]( 'AXOO1;AX005;AK013', ';')

image

1 Like
3

Thank you Harish, I'll try with this function.

Luis

4

Couple of ways

USE sqlteam
GO


declare @codes nvarchar(100) = 'AXOO1;AX005;AK013;'


create table #product(productcode nvarchar(10), ProductName nvarchar(50))
insert into #product
select top 100000 case 
			when column_id % 2 = 0 then 'AX' + cast(column_id as nvarchar(50))
			else 'AK' + cast(column_id as nvarchar(50))
	   end as productcode,
	   name
  from sys.all_columns 
  union
  select 'AXOO1', 'Chicken' union
  select 'AX005', 'Beef' union
  select 'AK013', 'Goat' 


select * 
  From #product p
  join DelimitedSplit8K(@codes,';') c on p.productcode = c.Item;

--table type approach
IF TYPE_ID(N'ProductCodeType') IS NOT NULL
	DROP TYPE [dbo].[ProductCodeType] 

CREATE TYPE [dbo].[ProductCodeType] AS TABLE(
	ProductCode nvarchar(10) NOT NULL
)
go

DECLARE @ProductCode  AS ProductCodeType;

insert into @ProductCode
 select 'AXOO1' union
  select 'AX005' union
  select 'AK013'
  

select * 
  From #product p
  join @ProductCode c on p.productcode = c.productcode;

drop table #product
1 Like
5

I have no problem with you posting the code but please leave the header in place. Thanks.

2 Likes
6

Sure Thing Jeff

Please excuse me .. I was not aware !!

7

Thanks. The big reason is because of the test code and the long history located in the header not to mention the usage examples. It helps build trust in the code when a DBA sees it. A lot of DBAs are "function phobics". A Developer stands a better chance getting the code in play when such a DBA is present because of the proper documentation and, perhaps, a little when they they see who wrote it.