i m passing one one variable with comma seperated integer values to IN
clause of select query

i.e.
declare @con varchar(1000)
set @con = '1,2,3'
select *
from
    tbl_QuarantineItemDetail
WHERE
    ContainerNo in (@con )

but it is not working.

can any one help me.

Dejan Sarka wrote this function
IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
   DROP FUNCTION dbo.TsqlSplit
GO

CREATE FUNCTION dbo.TsqlSplit
(@List As varchar(8000))
RETURNS @Items table (Item varchar(8000) Not Null)
AS
BEGIN
  DECLARE @Item As varchar(8000), @Pos As int
  WHILE DATALENGTH(@List)>0
  BEGIN
    SET @Pos=CHARINDEX(',',@List)
    IF @Pos=0 SET @Pos=DATALENGTH(@List)+1
    SET @Item =  LTRIM(RTRIM(LEFT(@List,@Pos-1)))
    IF @Item<>'' INSERT INTO @Items SELECT @Item
    SET @List=SUBSTRING(@List,@Pos+DATALENGTH(','),8000)
  END
  RETURN
END
GO

/* Usage example */
SELECT t1.*
  FROM TsqlSplit('10428,10429') AS t1


declare @inList varchar(50)
set @inList='10428,10429'

select od.* from [order details] od
INNER JOIN
         (SELECT Item
            FROM dbo.TsqlSplit(@InList)) As t
        ON od.orderid = t.Item
<ykpar***@gmail.com> wrote in message
Show quoteHide quote

http://www.sommarskog.se/dynamic_sql.html#List

Show quote Hide quote

ykpar***@gmail.com skrev:

Show quoteHide quoteUri gave you a solution, but I also want to tell you why your solution
DOESN'T work.

Exchange '@con' in your WHERE clause to its assigned value -->

WHERE
    ContainerNo in ('1,2,3' )

That is, you check whether ContainerNo is equal to '1,2,3' or not (you
do IN on a list with only one element). You want either "IN (1, 2, 3)"
or "IN ('1', '2', '3')" depending on datatype.

    /impslayer, aka Birger Johansson

thank you Uri Dimant
and all who replied
the function is working fine
it solved my problem

thank you very much for reply

Deadlock problem with insert trigger
Deadlocks "again"
Move DB by backup/restore or SP_Detach?
How to "Grant" a Trigger ???
Table comparison
"Timeout Expired" on large table change
Licensing by the month?
Installing SQL Server 2005
MSDE 2000 problems with linked server
Q: Diagram in SQL Server