How to parse a comma separated string into a SQL table

In SSRS, there is a limitation that you cannot pass a multivalue parameter to a stored procedure. I read a couple of ways to get around that, the best being this one from Mark Vaillancourt. The ‘Scooby Do’ references are pretty funny. Ruh roh.

http://markvsql.com/index.php/2010/01/jinkies-passing-multivalue-ssrs-parameters-to-stored-procedures

In the spirit of laziness, I wanted a slightly simpler answer. I prompt the use to enter a comma separated list of customers, and parse them into a table in SQL. Then, I join that table to the customer master table in my application. Or you could do a ‘WHERE CUSTNMBR IN (SELECT CUSTNMBR FROM @MYTABLE)’

Related Articles

... and you 'll find more on the SQL (General) Menu

 

declare @customerNumber varchar(100) 
declare @customers table (custnmbr varchar(15)) 
declare @customer varchar(15) 
declare @pos int 
    
set @customerNumber = '1234,qwer,zxv' 
set @pos = CHARINDEX(',',@CustomerNumber) 
    
while @pos > 0 begin 
    set @customer = LEFT(@customernumber,@pos-1) 
    set @customer = RTRIM(ltrim(@customer)) 
    insert into @customers(custnmbr) values (@customer) 
    set @customerNumber = SUBSTRING(@customerNumber,@pos + 1 , 100) 
    set @pos = CHARINDEX(',',@CustomerNumber) 
end 
    
set @customer = RTRIM(ltrim(@customernumber)) 
    
insert into @customers(custnmbr) values (@customer) 
select custnmbr from @customers

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences