4

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?

skyline01
  • 1,919
  • 8
  • 34
  • 55
  • You'll have to watch for SQL injection for sure. See this answer http://stackoverflow.com/questions/2944511/sql-server-in-clause-with-a-declared-variable – Matt Dec 18 '14 at 22:30
  • In SQL Server 2008 consider using [table-valued parameters](http://msdn.microsoft.com/en-us/library/bb510489.aspx) – Vladimir Baranov Dec 18 '14 at 22:33

3 Answers3

4

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
M.Ali
  • 67,945
  • 13
  • 101
  • 127
3

You can use dynamic sql for this. Use the replace function to get the correct NOT IN() argument:

DECLARE @test VARCHAR(10) = 'a,b,c,d'

DECLARE @sql NVARCHAR(MAX)

SELECT @sql = 'SELECT *
FROM #temp
WHERE label NOT IN (''' + REPLACE( @test ,',',''',''') + ''')'

EXEC sp_executesql @sql
Dave.Gugg
  • 6,561
  • 3
  • 24
  • 43
0

you can find the char or string in the whole string having delimiter as comma (,)

DECLARE @code VARCHAR(50) = 'c', @text varchar(100) = 'a,b,c,d,e'

SELECT CHARINDEX(',' + @code + ',', ',' + @text + ',') > 0
awh112
  • 1,466
  • 4
  • 22
  • 34
Rahul Sur
  • 1