CODEWITHSUNDEEP
c#asp.netsql-serverstored-proceduressplit
User987
User987

Reputation: 3823

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

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?

Upvotes: 1

Views: 257

Answers (3)

Dave C
Dave C

Reputation: 7392

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)

Upvotes: 2

Code Ranger
Code Ranger

Reputation: 401

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)

Upvotes: 2

user8061994
user8061994

Reputation:

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))

Upvotes: 2

Related Questions