6

Why the following SQL does not fetch me anything

DECLARE @Status AS VARCHAR(400)
SET @status = '''Closed'',''OPEN'''
select * from MYTABLE where status in(@status)

While as select * from MYTABLE where status in('Closed','Open') fetches me rows

rsapru
  • 688
  • 14
  • 30
  • 2
    i did check, but all of them were related to LINQ. Sorry if i missed any, could you please point me to a similar post – rsapru Mar 04 '11 at 10:41
  • Check this link might help. Yeah, you are right. It seems difficult to find one, but I am sure there are lots. Anyway got one link tough - http://stackoverflow.com/questions/2194120/formatting-a-string-for-a-sql-in-clause – Sachin Shanbhag Mar 04 '11 at 10:54

2 Answers2

4

Your first question checks if the value 'Closed','OPEN' exists in the database. The values is not expanded.

If you use SQL Server 2008 or later you can use Table Valued Parameters to achieve the same thing.

Albin Sunnanbo
  • 46,430
  • 8
  • 69
  • 108
4

You can if you want do some dynamic SQL but I think it is not really competitive.. 

  DECLARE   @Status nVARCHAR(400),
            @SQL nvarchar(500)

SET @status = '''Closed'''+','+'''OPEN'''
set @SQL = '
select * from [MYTABLE] where status in('+@status +')'

 exec sp_executesql @SQL
GO
bAN
  • 13,375
  • 16
  • 60
  • 93
  • 1
    Happy to help but take care of SQL injection with dynamic SQL if is the code for webApp.. – bAN Mar 04 '11 at 11:01