|
Home > Archive > MS SQL Server > March 2006 > IN clause with comma seperated values in select query
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
IN clause with comma seperated values in select query
|
|
| ykparmar@gmail.com 2006-03-16, 7:24 am |
| 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_QuarantineItemDe
tail
WHERE
ContainerNo in (@con )
but it is not working.
can any one help me.
| |
| Uri Dimant 2006-03-16, 7:24 am |
| 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(@Lis
t)+1
SET @Item = LTRIM(RTRIM(LEFT(@Li
st,@Pos-1)))
IF @Item<>'' INSERT INTO @Items SELECT @Item
SET @List=SUBSTRING(@Lis
t,@Pos+DATALENGTH(',
'),8000)
END
RETURN
END
GO
/* Usage example */
SELECT t1.*
FROM TsqlSplit('10428,104
29') 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
<ykparmar@gmail.com> wrote in message
news:1142506673.267795.77020@i39g2000cwa.googlegroups.com...
>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_QuarantineItemDe
tail
> WHERE
> ContainerNo in (@con )
>
> but it is not working.
>
> can any one help me.
>
| |
|
|
| impslayer 2006-03-16, 7:24 am |
|
ykparmar@gmail.com skrev:
> 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_QuarantineItemD
etail
> WHERE
> ContainerNo in (@con )
>
> but it is not working.
>
> can any one help me.
Uri 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
| |
| ykparmar@gmail.com 2006-03-16, 7:24 am |
| thank you Uri Dimant
and all who replied
the function is working fine
it solved my problem
thank you very much for reply
|
|
|
|
|