Passing an Array for WHERE IN in Stored Procedure

Transact-SQL
1

Hello I'm trying to find the best way in SQL Server 2012 to pass an array to execute a WHERE IN in a stored procedure

Below is my example, I use the variable @GradeFilter to emulate what should come as parameter. It works well if I ave a single element 'A' but if I didn't find the way to define multiple element 'A','B','C'

Thanks for any help

BTW : How can I format the code : Control-K doesn't work, it ask for an hyperlink, So I've used blockQuote but I guess this is not the right way

DECLARE @GradeFilter VARCHAR(MAX)
SET @GradeFilter='A','B'
SELECT
Item.StockHRK AS HRK
,COUNT(*) AS SQty
,AVG([GRS_SalesEUROPrice]) AS ASV

FROM Hapi.dbo.GRSFeedBack GRS
INNER JOIN Hapi.Dbo.Item on Item.uniquekey=GRS.uniquekey
WHERE GRS_SalesEUROPrice IS NOT NULL
AND GRS_SaleDate BETWEEN '20161201' AND '20170208'
AND (ISNULL(@GradeFilter,'')='' OR GRSGrade IN (@GradeFilter))
GROUP BY Item.StockHRK

2

Its all MarkDown here, which I find to be a PITA

Easiest way is:

    ```sql
    your code here
    ```

having 4 spaces on the front of each line may also work, but there are side effects with that approach (but for any code you don't format the bits that do have 4+ leading spaces get Rogered :frowning: )

You can't do

WHERE IN (@GradeFilter)

The common way of handling this is to pass the "list" as a single parameter, delimited with, say, comma.

You then "split" that list to create one-row-per-element - into a #TEMP table or @TableVariable

You then JOIN that #TEMP row-list to your query (instead of using IN)

Or you could use Dynamic SQL. I strongly advise discarding that idea, unless all else fails!

Or you could use a cheap and cheerful method, but that is SLOW. Fine for small data sets / one-off jobs.

OR GRSGrade IN (@GradeFilter)

becomes

OR ',' + @GradeFilter + ',' LIKE '%,' + GRSGrade + ',%'

So your Grade Filter becomes the string ",A,B," and you test if that contains your ",X," grade search string.

Alternatively, if your APP can cope with it, you can pass an Array, from your APP, as a SQL Table Variable (so to speak) such that your SProc has a @TableVariable parameter. I've never used that approach because we've never bothered to change our APP to have the capability to pass a local-array-as-SQL-table-variable

3

Thank you Kristen !
Well it indeed seems to be a PITA

Maybe the Best would be to use another procedure to parse my input parameter that could be A,B,C,'WHATEVER' and create the list dynamicaly

Something like

WHERE IN (SP_ParseList(@GradeFilter))

Would it be a good approach ?

4

Use a "standard" splitter function, such as DelimitedSplit8K (you can Google for the source to it).

DECLARE @GradeFilter VARCHAR(MAX)
SET @GradeFilter='A,B'

SELECT 
    Item.StockHRK AS HRK
   ,COUNT(*) AS SQty
  ,AVG([GRS_SalesEUROPrice]) AS ASV
FROM Hapi.dbo.GRSFeedBack GRS
INNER JOIN Hapi.Dbo.Item on Item.uniquekey=GRS.uniquekey
WHERE GRS_SalesEUROPrice IS NOT NULL
AND GRS_SaleDate >= '20161201' AND GRS_SaleDate < '20170209'
AND (ISNULL(@GradeFilter,'')='' OR 
     GRSGrade IN (SELECT Item FROM dbo.DelimitedSplit8K(@GradeFilter, ',')))
GROUP BY Item.StockHRK
5

or pass in your parm as xml and parse?

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
6

Sadly you can't call an SProc at that point, only a Function, and that's what @ScottPletcher is suggesting in using DelimitedSplit8K()

If this will be a recurring programming-need my advice would be to go the DelimitedSplit8K() route, discover how that solves this problem, and then you will have that knowledge and skill in future in similar situations

7

Thank you Kristen
I don't know about DelimitedSplit8K()

At the end I've writtem my own function for the purpose

USE [Touch]
GO

/****** Object: UserDefinedFunction [dbo].[SplitWhereIn] Script Date: 9/02/2017 21:05:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION [dbo].[SplitWhereIn]
(
@List varchar(2000),
@SplitOn varchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value varchar(100)
)
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin

Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))

Return

END

GO

And I use it like this

...
AND (ISNULL(@GradeFilter,'')='' OR GRSGrade IN (SELECT [Value] FROM dbo.SplitWhereIn(@GradeFilter,',')))

8

It does exactly the same are your function does, the only difference being that your function uses a Loop and DelimitedSplit8K is set-based. Your function will be very slow (increasingly so for large lists) and so will not scale well. DelimitedSplit8K will be much faster. So my suggestion would be that you adopt DelimitedSplit8K in place of your function and then that will be your defacto standard for all future such "string splitting".

9

Thank you Kirsten

Where can I found that Function ?
Is it something Native in SQL Server ?

10

http://www.sqlservercentral.com/Files/The%20New%20Splitter%20Functions.zip/9510.zip

11

For some reason, the link that Kristen posted doesn't seem operate correctly when it finally gets to SSC. Might as well learn how the splitter works, as well. Please see the following article for how it works and then scroll to the bottom of the article to the resources section to pick up the zip file.
http://www.sqlservercentral.com/articles/Tally+Table/72993/

12

Thank you all
Thank you JeffModen

13

Might be that a login (to SQLServerCentral) is required? I probably had a current login when I tested it.

14

Dunno. I'm an SSC user and it didn't work for me.