IN not working | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

IN not working

Hi, I have two table called Login and Group. Login table has the following records Logid Name
3N3 Group table has the following records Logid GroupName
‘1’,’2′,’3′ Test All the fields of both table are of nvarchar data type I used the query
select * from login where logid in(‘1′,’2′,’3’)
which gave the required result. But when using this query select * from login where logid in(select logid from Group)
nothing resulted What goes wrong? Madhivanan

SQL Server will recognize ‘1,’2′,’3′ as a single value not three different one since they are contained in a single column. So, to me the second table doesn’t seem to be normalized how it should be. With this your only option left is dynamic sql. See ———————–

use Dynamic SQL to do this..
DECLARE @Str VARCHAR(2000) SELECT @ID = LogID FROM TempDB..[Group] SET @Str = ‘select * from login where logid in ( ‘ + @ID + ‘)’
EXEC(@Str ) Thx

Thanks venu, your idea works well. FrankKalis,Why is it working in Dynamic SQL? @ID will also have ‘1’,’2′,’3′ which is to be considered as a single value. Madhivanan
Read the article, I referenced. It explains this in depth.
Btw, it also explains that in most cases sp_executeSQL is preferable over EXEC().