Passing list of values to stored procedure

Transact-SQL
1

Hi,
using sql 2012. Want to pass list of values to stored procedure
For example,need to fetch products with colors 'red,green,blue'
But in sp it seems to be not working while comparing like

in ('red,green,blue')

Any workaround for this?

2

You probably want to use a Table Valued Parameter.
Here is agreat tutorial on the topic: http://sommarskog.se/arrays-in-sql-2008.html

1 Like
3

That should work, but

IN (@MySProcParameter)

won't - or at least it won't work in the way you might be hoping.

Pass as table valued parameter (particularly if that is easy to do from the APP language you are using), or pass as a delimited string and use a Table Valued Splitter function to "split" the delimited list into a temporary table,. and then JOIN that temp table into your main query (that is more likely to be the desired solution with languages that don't lend themselves to the first option, or for command-lien / API type interfaces)

EDIT: My bad ... the original code won't work, it needs to be:

in ('red','green','blue')
4

Thanks @spaghettidba and @Kristen for the response.Let me clarify with an example

DECLARE @specTable TABLE
(
  ProductID int, 
  ProdName varchar(10),
  SpecType varchar(10),
  SpecValue varchar(25)

)

Insert into @specTable Values(
1,
'Prod1'
,'Color'
,'green'
),

(
2,
'Prod2'
,'Color'
,'Blue,chrome'
),

(
3,
'Prod3'
,'Color'
,'yellow'
),

(
4,
'Prod4'
,'Color'
,'red'
)
Select * from @specTable

Objective : get products with color red or green or blue
Expected result : prod1,prod2,prod4

If have a color list @colorList = 'red,green,blue', passed to the storedprocedure as parameter from the application,
how could the required records filtered ?

5

Using a Table Valued Splitter function, as I described earlier. If you can provide the ColorList as a Table Valued Parameter, instead of a delimited string, then you could use @spaghettidba solution

6

As @Kristen wrote, use a splitter. With DelimitedSplit8K by @JeffModen you can do (I belive this works on MSSQL 2008R2 and up):

select distinct
       a.*
  from spectable as a
       inner join dbo.DelimitedSplit8K(@colorlist,',') as b
               on ','+a.specvalue+',' like '%,'+b.item+',%'
 where a.spectype='Color'

In MSSQL 2016 where is a built-in function (string_split) which can be used like this:

select distinct
       a.*
  from spectable as a
       inner join string_split(@colorlist,',') as b
               on ','+a.specvalue+',' like '%,'+b.value+',%'
 where a.spectype='Color'

Personally I like DelimitedSplit8K best, as it provides the sequence number (itemnumber) in which each item in the string list occurs, which in some cases are very handy. It's slightly slower than the built-in function, but it rocks.

2 Likes
7

Aye.... thanks @bitsmed . I appreciate the reference.

And I'm right with you on thinking that MS came up a wee bit short on the splitter they released in 2016.

8

I think it is very difficult for folk to find the correct version via the main link. I've clicked on it, thought I'd gone round in circles between the Original and Update articles when actually the browser had pulled the file down as well. Perhaps, given how popular it is, it should have its own download page somewhere - GitHub or somesuch?

9

Thanks for the heads up and the good idea on that. I'll see what I can do.

10

Hi,

Here you go:-

Create FUNCTION [dbo].[SplitList]
(
--select * from SplitList('A,b,c')
@InputString NVARCHAR(max))
RETURNS @ValuesList TABLE
(
param NVARCHAR(255)
)
AS
BEGIN
DECLARE @ListValue NVARCHAR(max)
SET @InputString = @InputString + ','
WHILE @InputString != ' '
BEGIN
SELECT @ListValue = SUBSTRING(@InputString , 1, CHARINDEX(',', @InputString)-1)

INSERT INTO @ValuesList
SELECT LTRIM(RTRIM(@ListValue))
SELECT @InputString = SUBSTRING(@InputString, CHARINDEX(',', @InputString) + 1 , LEN(@InputString) - CHARINDEX(',', @InputString))
END

RETURN

END

DECLARE @specTable TABLE
(
ProductID int,
ProdName varchar(10),
SpecType varchar(10),
SpecValue varchar(25)

)

Insert into @specTable Values(
1,
'Prod1'
,'Color'
,'green'
),

(
2,
'Prod2'
,'Color'
,'Blue,chrome'
),

(
3,
'Prod3'
,'Color'
,'yellow'
),

(
4,
'Prod4'
,'Color'
,'red'
)

Normal SQL:

declare @listofvalues nvarchar(max)
set @listofvalues='green,red'
select * from @specTable
where SpecType='Color' and SpecValue in(
select * from dbo.SplitList(@listofvalues)
)

Stored Procedures:-

create proceduers sp_getResult
(
@listofvalues nvarchar(max)
)
As
Begin
select * from @specTable
where SpecType='Color' and SpecValue in(
select * from dbo.SplitList(@listofvalues)
)
End

That's all.

Thank you.

Regards,
Micheale

11

No! Performance will be dreadful. Use a SET BASED splitter function.

12

In past I have used xml successfully.

create proc products_sp
as
(
	@categories xml,
	@brands xml,
	@colors xml,
	@prices xml
)
begin
	/*
		@category xml
    	<Categories>   
	  <Category>
		<CategoryId>1</CategoryId>			
	  </Category> 
	  <Category>
		<CategoryId>2</CategoryId>    
	  </Category> 
	</Categories>
*/
create table #categories(categoryid int not null);
create table #brands(brandid int not null);
create table #colors(colorid int not null);
create table #prices(priceid int not null);
insert into #categories
select  T.C.value('(CategoryId/text())[1]','varchar(500)') as CategoryId
  from @categories.nodes('/Categories/Category') as T(C) 
select 
  from dbo.products p
  join dbo.brands b on p.Brandid = b.brandid
  join dbo.ProductColors pc on pc.productId = p.Productid
  join dbo.DimColors dc on pc.Colorid = = dc.Colorid
  join dbo.ProductPrices pp on pp.productId = p.productId
  join dbo.ProductCategories pcg on pcg.productId = p.productId
  left join #categories c on pcg.categoryid = c.categoryid
  left join #brands b on b.Brandid = p.brandid
end