Passing two strings into a stored procedure and splitting them via specific character to perform a query

Question

I have basic procedure which basically looks like following:

create procedure zsp_selectallupceans_list
    (@UPCList nvarchar(4000),
    @EANList nvarchar(4000))
as
    select *
    from data as dd 
    where dd.UPC in (--myUPC list) or dd.EAN in (--myEAN list)

This is the basic idea. Now I need to somehow split this string that I passed from my C# application and it would look like following for the UPC and EAN List:

 where dd.UPC in ('123','456','567') or dd.EAN in('1234','5542','412')

The UPCList parameter that is passed from C# application looks like:

'123,456,567' and eanlist: '1234,5542,412'

I have found a method which looks like this:

CREATE FUNCTION dbo.splitstring 
    (@stringToSplit VARCHAR(MAX))
RETURNS
    @returnList TABLE ([Name] [NVARCHAR](500))
AS
BEGIN
    DECLARE @name NVARCHAR(255)
    DECLARE @pos INT

    WHILE CHARINDEX(',', @stringToSplit) > 0
    BEGIN
        SELECT @pos  = CHARINDEX(',', @stringToSplit)  
        SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

        INSERT INTO @returnList 
            SELECT @name

        SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
    END

    INSERT INTO @returnList
        SELECT @stringToSplit

    RETURN
END

And the usage of this function is like following:

SELECT * FROM dbo.splitstring('91,12,65,78,56,789')

where the output is these numbers where they are split and output as a result.

Now I just need to somehow combine all this so that I can form a proper where statement based on passed parameter UPCList and EANList

Can someone help me out with this?

From C# you can use a DataTable as a Table Valued Parameter thats passed to the SP: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters - You then have a @tablevariable in the SP you can JOIN with or subquery with IN(). — Alex K., Aug 10 '18 at 16:24
I have added an additional answer with 2 performance improving recommendations. — Dave C, Aug 10 '18 at 17:13

score 2 · Accepted Answer · answered Aug 10 '18 at 16:24

2

Updating your stored proc as below should do the trick:

create procedure zsp_selectallupceans_list
(
@UPCList nvarchar(4000),
@EANList nvarchar(4000)
)
as

select *
from data  as dd 
where dd.UPC in (SELECT * FROM dbo.SplitString(@UPCList)) OR
dd.EAN in (SELECT * FROM dbo.SplitString(@EANList))

answered Aug 10 '18 at 16:24

the OR statement will ensure that if the result isn't found for UPC , it will be found for EAN ? – User987 Aug 10 '18 at 16:29
If a record is found that matches a UPC in your table **or** an EAN in your table, it will be returned as a result of your procedure, so yes. – Aug 10 '18 at 16:31
aha okay got it, what would be the most efficient way to remove duplicate UPC/EANs if there are any returned by the procedure? – User987 Aug 10 '18 at 16:41
I'm not familiar with the schema of your database, but this procedure should not return duplicate records (ie - if a record is found in both your UPC and your EAN list, it will only appear once in your results). That being said, if the table itself somehow allows for duplicates, your could use a `SELECT DISTINCT` statement in place of a regular `SELECT`, which would ensure uniqueness of all values specified after the `SELECT DISTINCT` clause. There are other ways to ensure uniqueness, I would need to know more about your schema to determine the "most efficient" way. – Aug 10 '18 at 16:49

score 2 · Answer 2 · answered Aug 10 '18 at 16:29

2

You pretty much have the answer: Compile and save the splitstring function and then your where clause will look like the following:

where dd.UPC in (Select Name From splitstring(--myUpcList)) or dd.EAN in (Select Name from splitstring(--myEanList)

answered Aug 10 '18 at 16:29

Code Ranger

401
3
7

score 2 · Answer 3 · answered Aug 10 '18 at 17:12

Here is an XML based function for string splitting, this method is much faster than the SUBSTRING method you already found. It is also recommended to use EXISTS instead of IN for performance improvement also, see here for more information on this.

CREATE FUNCTION [dbo].[SplitString]
(
  @string nvarchar(max),
  @delimiter nvarchar(5)
) RETURNS @t TABLE
(
  val nvarchar(500)
)
AS
BEGIN
  declare @xml xml
  set @xml = N'<root><r>' + replace(@string,@delimiter,'</r><r>') + '</r></root>'

  insert into @t(val)
  select 
    r.value('.','varchar(500)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END

To use:

SELECT *
FROM data t
WHERE EXISTS (SELECT 1 FROM dbo.SplitString(@UPCList,',') S1 WHERE t.UPC=S1.val) 
   OR EXISTS (SELECT 1 FROM dbo.SplitString(@EANList,',') S2 WHERE t.EAN=S2.val)

Passing two strings into a stored procedure and splitting them via specific character to perform a query

3 Answers3