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
————-
1N1
2N2
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. Seehttp://www.sommarskog.se/dynamic_sql.html ———————–
–Frank
http://www.insidesql.de
———————–

---

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

---

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().
———————–
–Frank
http://www.insidesql.de
———————–

---

]]>