How to parse a comma-delimited string variable in SQL

Question

I am working in SQL Server 2008. I have a stored proc that takes a parameter, called @test. This parameter is varchar(255). In this stored proc, I need to parse this string, convert each value into a string itself (there will be a variable number of values), and build a list to use in a NOT IN statement.

For example, suppose @test = 'a, b, c, d'. I need to send this parameter into my stored proc. There is a SELECT query in my stored proc that uses a NOT IN statement. For this example, I need this NOT IN statement to read NOT IN('a', 'b', 'c', 'd').

How do I accomplish this? Or, is this a bad practice?

Answer 1

Use Split function something along with NOT EXISTS operator almost always faster than NOT IN operator

Split Function

CREATE FUNCTION [dbo].[split]
    (
      @delimited NVARCHAR(MAX),
      @delimiter NVARCHAR(100)
    ) 
 RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
  DECLARE @xml XML
  SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

  INSERT INTO @t(val)
  SELECT  r.value('.','varchar(MAX)') as item
  FROM  @xml.nodes('/t') as records(r)
  RETURN
END

Test Data

DECLARE @Table TABLE (Vals INT)
INSERT INTO @Table VALUES (1), (2), (3), (4)

DECLARE @test VARCHAR(256) = '3,4,5,6'

SELECT * FROM @Table 
WHERE NOT EXISTS (SELECT 1
                  FROM [dbo].[split](@test , ',')
                  WHERE val = Vals)

Result

Vals
  1 
  2