0

I set my 

declare @area as varchar(500)

set @area = '''Area1'',''Area2'''

I tried to put it in WHERE clause, but it doesn't detect area. I am guessing there is something wrong with set @area =?

This is what I'm trying to bring it in:

where area in (@area)

Please help, thanks.

Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
Nguyenal07
  • 137
  • 2
  • 10
  • 3
    `area in (@area)` is the same as `area in ('''Area1''.''Area2''')`. So SQL Server is looking for an actual value in the table equal to the entire string: `'Area1','Area2'`. SQL Server doesn't know what an array is. If you want it to check multiple values you'll need to split that string up using a split function, or use dynamic SQL, or - preferably - by having them come in as separate values using a table-valued parameter. See http://sqlperformance.com/2012/07/t-sql-queries/split-strings and http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql. – Aaron Bertrand Apr 24 '15 at 19:02
  • If you can explain where `'''Area1'',''Area2'''` comes from and what control you have over that, it will be much easier to help. – Aaron Bertrand Apr 24 '15 at 19:15
  • @AaronBertrand I will use ASP to execute execute this sql. ASP will will bring in area as "'Area1','Area2','Area3''' – Nguyenal07 Apr 24 '15 at 21:37

5 Answers5

1

Since you've got a list of elements to compare against, use a table variable to accumulate the comparison data, e.g.:

DECLARE @area as TABLE(Name VARCHAR(50));
INSERT INTO @area(Name) VALUES ('Area1'), ('Area2');

You can then use this either:

SELECT * 
FROM MyTable
WHERE MyCol in (SELECT Name FROM @Area);

Or better, via a direct join (failed joins will be eliminated):

SELECT * 
FROM MyTable INNER JOIN @Area
ON MyTable.MyCol = @Area.Name;
StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • i cant use the table variable method, I'm trying to pass my asp area into sql – Nguyenal07 Apr 24 '15 at 21:43
  • You still can - here are [some solutions](http://stackoverflow.com/questions/337704/parameterize-a-sql-in-clause). You can also wrap the above into a PROC and then bind a Table Valued Parameter - ADO allows you to bind IEnumerables and DataTables to table parameters. – StuartLC Apr 25 '15 at 07:30
0

It should be a set rather then varchar variable:

where area in (select a from(values('Area1'),('Area2')) t(a))
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
0

Try something like 

declare @area as varchar(500)

set @area = 'Area1,Area2'

SELECT * 
FROM TableName
where area in (
              SELECT Split.a.value('.', 'VARCHAR(100)') AS Area
              FROM (SELECT  CAST ('<M>' + REPLACE(@area, ',', '</M><M>') 
                + '</M>' AS XML) AS String ) AS A
               CROSS APPLY String.nodes ('/M') AS Split(a)
               )
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • Careful, if an area has characters like `&` this will break. Also we don't know the OP has control over the input format (you changed `''Area1'',''Area2''` to `Area1,Area2`). – Aaron Bertrand Apr 24 '15 at 19:13
0

try use dynamic sql like-

declare @area as varchar(500)
set @area = 'Area1,Area2'
DECLARE @sql varchar(500)
set @sql='SELECT *
FROM [dbo].[tbl] AS t
WHERE area in ('+@area+')'
exec (@sql)

or

declare @area as varchar(500)
set @area = '''Area1'',''Area2'''
DECLARE @sql varchar(500)
set @sql='SELECT *
FROM [dbo].[tbl] AS t
WHERE area in ('+@area+')'
PRINT @sql
exec (@sql)
Dudi Konfino
  • 1,126
  • 2
  • 13
  • 24
0

Result of your code

Query

declare @area as varchar(500)
set @area = '''Area1'',''Area2'''

Enter

print 'where area in (' + @area + ')'

Result

where area in ('Area1','Area2')
Darshit Shah
  • 629
  • 5
  • 5