Multiple variables to an IN sstatement

How can I pass a list of comma seperated values to an IN statememnt?
ie
@.parameter = '1,2,3,4'
select * from table_name where column_name in (@.parameter)
This can be executed dynamically, but is there any other method?In your stored procedure declare a table to hold the data.
Parse the input paramater into the table.
DECLARE @.InList table
( parm int)
)
parse the parameter
Select * from table_name where column_name in (select parm from @.InList)|||No, you cannot do that
1)
Dejan Sarka has posted this script
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
2)
declare @.sql varchar(50)
set @.sql='1,2,4'
select * from table where charindex(',' + id+ ',',','+ @.sql+',')>0
<newscorrespondent@.charter.net> wrote in message
news:vXEEg.27$Lo3.3@.newsfe07.lga...
> In your stored procedure declare a table to hold the data.
> Parse the input paramater into the table.
> DECLARE @.InList table
> ( parm int)
> )
> parse the parameter
> Select * from table_name where column_name in (select parm from @.InList)|||pravin.nagarajan@.gmail.com wrote:
> How can I pass a list of comma seperated values to an IN statememnt?
> ie
> @.parameter = '1,2,3,4'
> select * from table_name where column_name in (@.parameter)
> This can be executed dynamically, but is there any other method?
>
I have a short post on my web site describing how to do this, but the
site is currently being reconstructed. You can find the original post
in Google's cache by searching for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/FindingTheLatestValue"
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks I found this solution very helpful.It works
Uri Dimant wrote:
> No, you cannot do that
> 1)
> Dejan Sarka has posted this script
> 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
> 2)
> declare @.sql varchar(50)
> set @.sql='1,2,4'
> select * from table where charindex(',' + id+ ',',','+ @.sql+',')>0
>
> <newscorrespondent@.charter.net> wrote in message
> news:vXEEg.27$Lo3.3@.newsfe07.lga...
> > In your stored procedure declare a table to hold the data.
> > Parse the input paramater into the table.
> >
> > DECLARE @.InList table
> > ( parm int)
> > )
> >
> > parse the parameter
> >
> > Select * from table_name where column_name in (select parm from @.InList)sql