variable inside IN clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

variable inside IN clause

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-27 : 10:19:28

Hi
Can you see why I do not get any records back?
Thanks

Definitely there are data in the table.

I think the problem is in the IN statement. If I take out the variable @Filtered from the IN statement and place with hardcoded values i.e. the ones in the set statement, then the query returns data
declare @Filtered varchar(max)

set @Filtered = char(39) + 'roberts' + CHAR(39) + ',' + char(39) + 'peters' + CHAR(39)

SELECT field1, field2 = field3 + ' XXX', field7, Value = FieldX
FROM tblMain
WHERE ACField = 'Bonds' and field3 IN (@Filtered)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-27 : 10:49:18

you cant use variable in in like this. you need to either use dynamic sql of this below


SELECT field1, field2 = field3 + ' XXX', field7, Value = FieldX
FROM tblMain
WHERE ACField = 'Bonds' and CHAR(39) + ',' + @Filtered + CHAR(39) + ','  LIKE '%' + CHAR(39) + ',' + field3  + CHAR(39) + ',%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-29 : 02:18:28

quote:
Originally posted by visakh16

you cant use variable in in like this. you need to either use dynamic sql of this below

SELECT field1, field2 = field3 + ' XXX', field7, Value = FieldX
FROM tblMain
WHERE ACField = 'Bonds' and CHAR(39) + ',' + @Filtered + CHAR(39) + ',' LIKE '%' + CHAR(39) + ',' + field3 + CHAR(39) + ',%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-29 : 07:24:58

Just watch for the performance implications of visakh16's option, it's not the optimal way of doing things. If you're going the Dynamic SQL route, watch for SQL injection.

One of the better ways to do this is to find a split function (google Jeff Moden Split) and use the delimited string with that.

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-29 : 14:34:27

this is an example of Gail's split functionality

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources