0

My variable looks like this:

DECLARE @number AS NVARCHAR(1000)
SET @number = '1,2,3,4,5,6'

What I have in my WHERE statement is this:

WHERE V.client IN (@number)

My V.Client column is an integer. What I am trying to do is remove the '' from the @number and make it look like 1,2,3,4,5,6. I have tried to do this, 

',' + @number + ',' LIKE '%,' + CAST(V.client AS VARCHAR) + ',%'

but it only returns results for 1 and not the other numbers.

Does anyone have any ideas as to what I can do?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
bgrow11
  • 31
  • 8
  • 3
    Pass in a table valued parameter is one option. Or use dynamic sql. – Sean Lange Sep 13 '18 at 16:11
  • Use a "splitter()" function to turn the string parameter into a table add join against that. There are a few splitter functions here. – Brian Sep 13 '18 at 16:15
  • Possible duplicate of [Parameterize an SQL IN clause](https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) – HABO Sep 13 '18 at 18:02
  • Don't use delimited strings in SQL. Just Don't. There are other, better ways. Using A table valued parameter or an xml parameter are probably the best options, another would be using a string splitting function, string manipulation like the one you've tried and dynamic sql are also options, though probably the worst. – Zohar Peled Sep 13 '18 at 19:04

2 Answers2

3

Another option

DECLARE @number AS nvarchar(1000)
SET @number = '1,2,3,4,5,6'

...
WHERE V.client IN (
                    Select RetVal = B.i.value('(./text())[1]', 'int')
                    From  (Select x = Cast('<x>' + replace(@Number,',','</x><x>')+'</x>' as xml).query('.')) as A 
                    Cross Apply x.nodes('x') AS B(i)
                  )

If 2016+, use string_split()

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
0

Dynamic sql is the most common option, here's a snippet how to use it 

DECLARE @number AS nvarchar(1000)
SET @number = N'1,2,3,4,5,6'

declare @cmd nvarchar(1000) = N'select case when 1 in ' + Quotename(@number, '()') + ' then 1 else 0 end'

exec sp_executesql @cmd

which will prepare following query

select case when 1 in (1,2,3,4,5,6) then 1 else 0 end

Other options you can consider: Split the string and use standard sql queries. In new sql server there is function string_split which make exacly what you want https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017. In older versions you can create custom fuction doing exactly the same thing How do I split a string so I can access item x?

lukaszberwid
  • 1,097
  • 7
  • 19