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

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:[vbcol=seagreen]
> 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...

Multiple values int textbox...

Hi,
I need to display the result set in a textbox,
if there are multiple rows i need to show them as comma separated..
Can anyone give me idea of how it can be achieved..
Thanks in advance,I can't think of an easy way.
You need to get the resultset concatanating, not do it in report layout.
You could use a cursor (effectively a loop) in the SQL query - get the
result set and concatanate the rows into a variable and output the
variable as the result set. Cursors are quite slow though.
You could do the same thing in VB code using ADO if that's your thing.
Chris
CCP wrote:
> Hi,
> I need to display the result set in a textbox,
> if there are multiple rows i need to show them as comma separated..
> Can anyone give me idea of how it can be achieved..
> Thanks in advance,sql

Multiple rows into single column

I'm working on a Reports and I need to combine data from multiple
rows (with the same EventDate) into one comma separated string. This is how the data is at the moment:

EmployeeID EventDate
2309 2005-10-01 00:00:00.000
2309 2005-10-01 03:44:50.000
2309 2005-10-01 08:59:00.000
2309 2005-10-01 09:29:44.000

I need it in the following format:

EmpID EventDate EventTime
2309 2005-10-01 00:00:00,03:44:50,08:59:00,09:29:44

There is no definite number of EventTime per Employee for a particular EventDate. And i don't want to use Iterative Methods.

Thanks & Regards
Rajan

Hi Rajan,

If you have access to sql server magazine take a look at this article, http://www.sqlmag.com/Article/ArticleID/93907/sql_server_93907.html

You can see the code even if you don't have membership so should still be useful. Listing 3 will probably be the most useful, although it is not particularly efficient.

Hope this helps.

Chris

|||

Another alternative might be something like:

declare @.mockup table
( EmployeeId integer,
EventDate datetime
)
insert into @.mockup values (2308, '1/1/2005')
insert into @.mockup values (2309, '10/1/2005')
insert into @.mockup values (2309, '10/1/2005 3:44:50')
insert into @.mockup values (2309, '10/1/2005 8:59')
insert into @.mockup values (2309, '10/1/2005 9:29:44')
insert into @.mockup values (2310, '11/1/2005')

select employeeId,
replace(replace(
( select e.eventDate as [data()]
from @.mockup e
where a.employeeId = e.employeeId
order by e.eventDate
for xml path ('')
), ' ', ','), 'T', ' ')
as EventDates
from @.mockup a
group by employeeId
order by employeeId

-- employeeId EventDates
-- -- -
-- 2308 2005-01-01 00:00:00
-- 2309 2005-10-01 00:00:00,2005-10-01 03:44:50,2005-10-01 08:59:00,2005-10-01 09:29:44
-- 2310 2005-11-01 00:00:00