3

I would like to do something like this : 

DECLARE @list nvarhcar(200) SET @list = 'VALUE1,VALUE2,VALUE3'

Select * from foo where field in (@list)

Until today I solved using sp_executeSQL procedure, my own database function csv2table and subselect, but I think that is not very clean.

Is there any way to solve without use dynamic sql ? Is there a direct method?

I'm using Microsoft Sql Server 2005.

Thanks!

arturn
  • 725
  • 2
  • 11
  • 25
  • As soon as you realise that the content of `field` might, in fact, contain commas, you should realise why SQL doesn't magically rip apart your single string and treat it as 3 separate values. – Damien_The_Unbeliever Dec 15 '11 at 11:43
  • Why can't you use Dynamic SQL? – aF. Dec 15 '11 at 11:49
  • There's 2 major flaws with dynamic SQL, especially when being used in stored procedures which -given the presence of parameters- probably is the case here. 1) the user needs SELECT rights on the foo table (this can be worked around with EXECUTE AS, I know) 2) the @list parameter might contain something like : `NULL); ;declare @x int;SELECT @x=(1`. Assuming the dyn-sql part is a naive `EXEC ('SELECT * FROM foo WHERE field in (' + @list + ')')` this would result in more trouble than you bargained for.. – deroby Dec 15 '11 at 12:06
  • possible duplicate of [SQL - using a variable for an IN clause](http://stackoverflow.com/questions/1948351/sql-using-a-variable-for-an-in-clause) – Pondlife Dec 15 '11 at 13:20

7 Answers7

2

Would you please try as below: thanks

DECLARE @list nvarchar(200) 
SET @list = 'VALUE1,VALUE2,VALUE3'

SELECT * FROM foo WHERE CHARINDEX(',' + field + ',', ',' + @list + ',') > 0
Elias Hossain
  • 4,410
  • 1
  • 19
  • 33
0

If the datatype for the field is nchar or varchar, the IN() operator will look for value like

IN('VALUE1','VALUE2','VALUE3', etc..)

So you set your @list based on that

Curtis
  • 101,612
  • 66
  • 270
  • 352
Robin Michael Poothurai
  • 5,444
  • 7
  • 23
  • 36
  • Could you elaborate abit. AFAIK the @list is provided by the user and contains a comma-separated list of values that you want to match with a column. This without using a UDF or dyn-sql... – deroby Dec 15 '11 at 12:08
  • I mean use like IN(REPLACE(''''+ rtrim(@list) +'''', ',', ''',''')) – Robin Michael Poothurai Dec 15 '11 at 13:40
  • Then you'd still need to execute it in dynamic sql. – deroby Dec 15 '11 at 13:51
  • @deroby you mean, SELECT * FROM tblname WHERE fieldname IN(REPLACE(''''+ rtrim(@list) +'''', ',', ''',''')) is dynamic sql. and to handle with variable in stored procedure, based on input values we have to process query and above query is not a complicated dynamic sql. Even we no need to run EXEC to run this query – Robin Michael Poothurai Dec 15 '11 at 14:05
  • I'm not saying it's complicated, it's just that you DO need EXEC() (or sp_executesql) to run this. – deroby Dec 15 '11 at 15:03
0

Create Part A as an UDF

/* Part A */
DECLARE @list Varchar(max) = 'VALUE1,VALUE2,VALUE3'
DECLARE @tmpTbl_Values Table  (ID varchar(50)) 

Set @list =  @list + ',' 
-- Indexes to keep the position of searching
Declare @Pos1 Int
Declare @pos2 Int

-- Start from first character 
Set @Pos1=1
Set @Pos2=1
While @Pos1<Len(@list)
Begin
Set @Pos1 = CharIndex(',',@list,@Pos1)
Insert @tmpTbl_Values Select  Cast(Substring(@list,@Pos2,@Pos1-@Pos2) As varchar(50))
-- Go to next non comma character
Set @Pos2=@Pos1+1
-- Search from the next charcater
Set @Pos1 = @Pos1+1
End 


/* Part B */
SELECT      * 
FROM        foo A
INNER JOIN  @tmpTbl_Values B
ON          AB.ID = B.ID
Renjith Kumar
  • 149
  • 3
0

One alternative:

Select * from foo where '%,' + field + ',%' like ',' + (@list) + ','

This is even "dirtier" than the existing approach.

Ideally, I would suggest changing the query string to:

Select * from foo where field in ()

then reading through the list of values in @list, inserting a ? for each value (separated by commas) and binding each value to that parameter marker. However, I don't know how to achieve this in dynamic SQL alone.

  • Forgotten about this one (LIKE). Why would this be 'dirtier' ? I've used this too in the past, and it works relatively well as long as there are no leading/trailing space issues in the @list content. (and it's not dyn-sql either) – deroby Dec 15 '11 at 13:11
  • Perhaps not "dirtier", but probably less efficient - an "in" clause should be able to use suitable indexes, but a "like" clause with `%` preceding the selection value won't. –  Dec 15 '11 at 13:15
  • 1
    True. But might not be an issue on small lists and few records. The UDF will have some overhead too after all. But I agree that if you're going to `LIKE` over 100 mio rows with a 2000 character string things might get slow quickly. (For fun I just tested with a lean (ID + field) 20 mio table and 400 char (10 values) @test and it took 23326ms using the LIKE -not that awful after all but still not something you want to do over and over again. However, the UDF takes 19304ms too ! But if I index foo on field (UQ, clustered) it takes only 218ms for the UDF while the LIKE is totally unaffected) – deroby Dec 15 '11 at 13:49
0

A possible approach is to do the following:

SELECT * FROM foo WHERE CHARINDEX(',' + field + ',', ',' + @list + ',') > 0

This is assuming that the data in the field has no comma in it

Mark Harrell
  • 331
  • 1
  • 3
0

If you deal with a string field, you could use something like this..

DECLARE @csv varchar(50) = 'item1,item2, item3, item4 ,item5'
SELECT * FROM foo WHERE PATINDEX('%' + Field + '%', @csv) > 0
DanielM
  • 939
  • 6
  • 4
-1

In SQL 2005 you can use a custom UDF to parse a delimited list into a table - please see this article http://www.sommarskog.se/arrays-in-sql-2005.html

Barry Kaye
  • 7,682
  • 6
  • 42
  • 64
  • I'm not the downvoter, but from the name of the function csv2table, I suspect this is what the OP is already doing. –  Dec 15 '11 at 11:58
  • Agreed, but then the Dyn-SQL answer should be down-voted too I guess =) – deroby Dec 15 '11 at 12:00