How to split parameter in stored procedure

Question

This is the stored procedure:

CREATE PROCEDURE [dbo].[StoredProcedure]
    @FILTERNAME varchar(100) = ''
AS
    IF @FILTERNAME <> ''
    BEGIN
        SELECT 
            CODE, NAMEen, NAMEkr 
        FROM 
            (SELECT 
                 OT.CODE, OT.NAMEen, OT.NAMEkr, OT.Sortkey 
             FROM 
                 OptionTable OT
             INNER JOIN 
                 ConditionTable CT ON OT.CODE = CT.CODE
             INNER JOIN 
                 MasterTable MT ON CT.DevCode = MT.DevCode  
                                AND CT.PlanCode = MT.PlanCode 
             WHERE 
                 MT.ProductName LIKE '%' + @FILTERNAME + '%'
             GROUP BY 
                 OT.CODE, OT.NAMEen, OT.NAMEkr, OT.Sortkey) TBL
        ORDER BY 
            Sortkey
    END

This stored procedure is called from vb6. I cant touch the vb6's code so I need to fix this.

Until now, the parameter was like 'XXX', but it's going to be like 'A,BB,CCC,ZZZZ'.

I need to split the parameter's string by , and change the condition like this: when the parameter is something like this 'A,BB,CCC,ZZZZ', then use this WHERE condition:

WHERE MT.ProductName LIKE '%' + A + '%'
   or MT.ProductName LIKE '%' + BB + '%'
   or MT.ProductName LIKE '%' + CCC + '%'
   or MT.ProductName LIKE '%' + ZZZZ + '%'

Can someone help me? Thanks.

You might need to do a string split function on `@FILTERNAME`. Then use something like `EXISTS`. — ZLK, Jun 20 '18 at 03:17
This was asked hundred times before, please start with some research! The linked answer has got ver high rated but outdated approaches, I'd suggest to look for some younger answers there. The ultimative source is [Aaron Bertrand's string-split article](https://sqlperformance.com/2012/07/t-sql-queries/split-strings) — Shnugo, Jun 20 '18 at 07:37

score 1 · Accepted Answer · answered Jun 20 '18 at 04:30

try this

CREATE proc [dbo].[StoredProcedure]
@FILTERNAME varchar(100) = ''
AS
IF @FILTERNAME <> ''
BEGIN

declare @x XML 
declare @Result as table(SingleCriteria varchar(max))
SELECT @x = CAST('<A>'+ REPLACE(@FILTERNAME,',','</A><A>')+ '</A>' AS XML)
INSERT INTO @Result            
SELECT t.value('.', 'varchar(max)') AS inVal
FROM @x.nodes('/A') AS x(t)



SELECT 
    CODE, NAMEen, NAMEkr 
FROM 
    (SELECT 
         OT.CODE, OT.NAMEen, OT.NAMEkr, OT.Sortkey 
     FROM 
         OptionTable OT
     INNER JOIN 
         ConditionTable CT ON OT.CODE = CT.CODE
     INNER JOIN 
         MasterTable MT ON CT.DevCode = MT.DevCode  
                        AND CT.PlanCode = MT.PlanCode 
    JOIN
         @Result RS ON 1=1
     WHERE 
         MT.ProductName LIKE '%' + RS.SingleCriteria + '%'
     GROUP BY 
         OT.CODE, OT.NAMEen, OT.NAMEkr, OT.Sortkey) TBL
ORDER BY 
    Sortkey
END

Convert Comma Separated String to Table

If `@FILTERNAME` might contain forbidden characters (like `<, > or &`), this will fail. You can change this `REPLACE(@FILTERNAME, ...` to this `REPLACE((SELECT @FILTERNAME AS [*] FOR XML PATH('')), ...`. This approach will translate all forbidden characters to XML entities implicitly. — Shnugo, Jun 20 '18 at 07:56

score 1 · Answer 2 · answered Jun 20 '18 at 05:01

You can use from This for split parameter

CREATE FUNCTION [dbo].[splitstring] ( @stringToSplit NVARCHAR(MAX),@SChar CHAR(1))
RETURNS
@returnList TABLE (ID INT identity(1,1),[Name] [nvarchar] (500))
AS
BEGIN

DECLARE @name NVARCHAR(255)
DECLARE @pos INT

WHILE CHARINDEX(@SChar, @stringToSplit) > 0
BEGIN
SELECT @pos  = CHARINDEX(@SChar, @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

String-split with a `WHILE` loop and a *multi-statement TVF* is outdated for centuries. Throw this away and read [this](https://sqlperformance.com/2012/07/t-sql-queries/split-strings) — Shnugo, Jun 20 '18 at 07:52

score 0 · Answer 3 · answered Jun 20 '18 at 05:08

My Suggestion for you:

Because There is no ready made Split function in SQL server, so we need to create user defined function.

like this:

CREATE FUNCTION Split (
      @InputString                  VARCHAR(8000),
      @Delimiter                    VARCHAR(50)
)

RETURNS @Items TABLE (
      Item                          VARCHAR(8000)
)

AS
BEGIN
      IF @Delimiter = ' '
      BEGIN
            SET @Delimiter = ','
            SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
      END

      IF (@Delimiter IS NULL OR @Delimiter = '')
            SET @Delimiter = ','

--INSERT INTO @Items VALUES (@Delimiter) -- Diagnostic
--INSERT INTO @Items VALUES (@InputString) -- Diagnostic

      DECLARE @Item           VARCHAR(8000)
      DECLARE @ItemList       VARCHAR(8000)
      DECLARE @DelimIndex     INT

      SET @ItemList = @InputString
      SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      WHILE (@DelimIndex != 0)
      BEGIN
            SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
            INSERT INTO @Items VALUES (@Item)

            -- Set @ItemList = @ItemList minus one less item
            SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
            SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      END -- End WHILE

      IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
      BEGIN
            SET @Item = @ItemList
            INSERT INTO @Items VALUES (@Item)
      END

      -- No delimiters were encountered in @InputString, so just return @InputString
      ELSE INSERT INTO @Items VALUES (@InputString)

      RETURN

END -- End Function
GO

---- Set Permissions
--GRANT SELECT ON Split TO UserRole1
--GRANT SELECT ON Split TO UserRole2
--GO

Source of above function this answer

and then try this Query:

DECLARE @tbl table(name nvarchar(8000))

INSERT @tbl SELECT * FROM dbo.Split(@FILTERNAME, ',');

SELECT 
    CODE, NAMEen, NAMEkr 
FROM 
    (SELECT 
         OT.CODE, OT.NAMEen, OT.NAMEkr, OT.Sortkey 
     FROM 
         OptionTable OT
     INNER JOIN 
         ConditionTable CT ON OT.CODE = CT.CODE
     INNER JOIN 
         MasterTable MT ON CT.DevCode = MT.DevCode  
                        AND CT.PlanCode = MT.PlanCode 
     INNER JOIN @tbl tbl ON tbl.name LIKE '%' + MT.ProductName + '%' 
     GROUP BY 
         OT.CODE, OT.NAMEen, OT.NAMEkr, OT.Sortkey) TBL
ORDER BY 
    Sortkey
END

How to split parameter in stored procedure

3 Answers3