InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 20:07:45 GMT20http://www.sqlservercentral.com/Forums/Topic1261985-145-1.aspxThanks Gail, I got it from your point. Thanks to others as well for describing.Fri, 09 Mar 2012 05:23:32 GMTsqlnaivehttp://www.sqlservercentral.com/Forums/Topic1261985-145-1.aspxBecause select * from #t1 where id in(@p2) is completely and totally equivalent to select * from #t1 where id = @p2. Each item in an IN (variable, parameter or constant) is considered to be a single valueTo specify variables in an IN, it would be like this: select * from #t1 where id in(@p1, @p2, @p3, @p4)Fri, 09 Mar 2012 03:39:59 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1261985-145-1.aspxI agree but why does not it works like this ? I mean is there something more precise or some doc where I can get deep understanding on this ?Fri, 09 Mar 2012 03:04:24 GMTsqlnaivehttp://www.sqlservercentral.com/Forums/Topic1261985-145-1.aspxBecause you need to run it as Dynamic SQL, using the EXEC command:[code="sql"]EXEC(N'SELECT * FROM #Test1 WHERE Test_Text IN (' + @P1 + ')');[/code]What you have is SQL probably attempting to find a match to the entire varchar ''xyz','tln','tprq'', not three seperate values.Fri, 09 Mar 2012 02:46:20 GMTBrainDonorhttp://www.sqlservercentral.com/Forums/Topic1261985-145-1.aspxBecause, as Gianluca and BrainDonor have already mentioned, you need to use a split function or dynamic SQL. Make sure you understand the [url=http://www.sommarskog.se/dynamic_sql.html]implications [/url]of the latter.JohnFri, 09 Mar 2012 02:42:16 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1261985-145-1.aspxcreate table #t1(id varchar(5))insert into #t1 values('xyz'), ('kkl'), ('tln'), ('cxz'), ('dfa'), ('tprq')select * from #t1Declare @p1 varchar(50) = 'xyz,tln,tprq'Declare @p2 varchar(50) = ''''+replace(@p1, ',', ''',''')+''''select @p1select @p2select * from #t1 where id in(@p2)Why teh last select does not return any value inspite of @p2 having the correct presentation of condition ?Fri, 09 Mar 2012 02:31:41 GMTsqlnaivehttp://www.sqlservercentral.com/Forums/Topic1261985-145-1.aspxAs Gianluca has shown, one way to do this is to split @P1, because it is no use in it's current format as a parameter. Col1 would have to have a value of 'xyz,tln,tprq' in one row.Another option is to use Dynamic SQL, but this would require a change in the format of @P1 - this may be an option for you. Pay attention to the number of single quotes required in the value of @P1:[code="sql"]CREATE TABLE #Test1( Test_Text VarChar(20) ); INSERT INTO #Test1(Test_Text)VALUES('abc'),('cde'),('fgh'),('xyz'),('123'),('tln'),('456'),('tprq');DECLARE @P1 VarChar(30);SET @P1 = '''xyz'''+','+'''tln'''+','+'''tprq''';SELECT @P1;EXEC(N'SELECT * FROM #Test1 WHERE Test_Text IN (' + @P1 + ')');[/code]Tue, 06 Mar 2012 02:19:35 GMTBrainDonorhttp://www.sqlservercentral.com/Forums/Topic1261985-145-1.aspxUse a split function: [url]http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]Tue, 06 Mar 2012 01:51:08 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1261985-145-1.aspxhow can i achieve this?Declare @p1 = 'xyz,tln,tprq'select * from table1 where col1 in('xyz','tln','tprq')Tue, 06 Mar 2012 00:47:20 GMTsqlnaive