«

I have a stored procedure that I am executing with this SQL
osp_instrument_swap @InstrumentFrom = 'testname', @InstrumentIDTo = 37,
@Schedules = '593, 594, 596, 598, 599, 600, 601, 602, 603, 604, 605, 606,
607, 608, 609, 610, 611, 612, 613, 616'

the @Schedules are ID fields in a table that are integers. I would like to
do an Update blah, Where ScheduleID in (@Schedules) instead of looping. How
can I do this? I have to sent the values to the sproc as text, right? Where
do I covert to the int data type and how?

Thanks for your help
Mike

http://www.aspfaq.com/2448

--
http://www.aspfaq.com/
(Reverse address to reply.)




"Mike D" <> wrote in message
news:B1875174-468E-4A76-A43E-...
> I have a stored procedure that I am executing with this SQL
> osp_instrument_swap @InstrumentFrom = 'testname', @InstrumentIDTo = 37,
> @Schedules = '593, 594, 596, 598, 599, 600, 601, 602, 603, 604, 605, 606,
> 607, 608, 609, 610, 611, 612, 613, 616'
>
> the @Schedules are ID fields in a table that are integers. I would like
to
> do an Update blah, Where ScheduleID in (@Schedules) instead of looping.
How
> can I do this? I have to sent the values to the sproc as text, right?
Where
> do I covert to the int data type and how?
>
> Thanks for your help
> Mike

Am I missing something? I read that and don't see anything that applies to
my problem. Did you post the right link?

Thanks again
Mike

"Aaron [SQL Server MVP]" wrote:

> http://www.aspfaq.com/2448
>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
>
>
> "Mike D" <> wrote in message
> news:B1875174-468E-4A76-A43E-...
> > I have a stored procedure that I am executing with this SQL
> > osp_instrument_swap @InstrumentFrom = 'testname', @InstrumentIDTo = 37,
> > @Schedules = '593, 594, 596, 598, 599, 600, 601, 602, 603, 604, 605, 606,
> > 607, 608, 609, 610, 611, 612, 613, 616'
> >
> > the @Schedules are ID fields in a table that are integers. I would like
> to
> > do an Update blah, Where ScheduleID in (@Schedules) instead of looping.
> How
> > can I do this? I have to sent the values to the sproc as text, right?
> Where
> > do I covert to the int data type and how?
> >
> > Thanks for your help
> > Mike
>
>
>

Mike D wrote:
> I have a stored procedure that I am executing with this SQL
> osp_instrument_swap @InstrumentFrom = 'testname', @InstrumentIDTo =
> 37, @Schedules = '593, 594, 596, 598, 599, 600, 601, 602, 603, 604,
> 605, 606, 607, 608, 609, 610, 611, 612, 613, 616'
>
> the @Schedules are ID fields in a table that are integers. I would
> like to do an Update blah, Where ScheduleID in (@Schedules) instead
> of looping. How can I do this? I have to sent the values to the
> sproc as text, right? Where do I covert to the int data type and how?
>
> Thanks for your help
> Mike
This is relevant:
http://www.sommarskog.se/arrays-in-sql.html

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Sorry.
http://www.aspfaq.com/2248

--
http://www.aspfaq.com/
(Reverse address to reply.)




"Mike D" <> wrote in message
news:5401A443-9812-4E7B-84D2-...
> Am I missing something? I read that and don't see anything that applies
to
> my problem. Did you post the right link?
>
> Thanks again
> Mike
>
> "Aaron [SQL Server MVP]" wrote:
>
> > http://www.aspfaq.com/2448
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "Mike D" <> wrote in message
> > news:B1875174-468E-4A76-A43E-...
> > > I have a stored procedure that I am executing with this SQL
> > > osp_instrument_swap @InstrumentFrom = 'testname', @InstrumentIDTo =
37,
> > > @Schedules = '593, 594, 596, 598, 599, 600, 601, 602, 603, 604, 605,
606,
> > > 607, 608, 609, 610, 611, 612, 613, 616'
> > >
> > > the @Schedules are ID fields in a table that are integers. I would
like
> > to
> > > do an Update blah, Where ScheduleID in (@Schedules) instead of
looping.
> > How
> > > can I do this? I have to sent the values to the sproc as text, right?
> > Where
> > > do I covert to the int data type and how?
> > >
> > > Thanks for your help
> > > Mike
> >
> >
> >

Thanks to both of you for your replies. It helps me solve the problem. Does
the new SQL Server (2005?) have any better way to handle arrays than SQL
Server 2000??

Thanks
Mike


"Bob Barrows [MVP]" wrote:

> Mike D wrote:
> > I have a stored procedure that I am executing with this SQL
> > osp_instrument_swap @InstrumentFrom = 'testname', @InstrumentIDTo =
> > 37, @Schedules = '593, 594, 596, 598, 599, 600, 601, 602, 603, 604,
> > 605, 606, 607, 608, 609, 610, 611, 612, 613, 616'
> >
> > the @Schedules are ID fields in a table that are integers. I would
> > like to do an Update blah, Where ScheduleID in (@Schedules) instead
> > of looping. How can I do this? I have to sent the values to the
> > sproc as text, right? Where do I covert to the int data type and how?
> >
> > Thanks for your help
> > Mike
> This is relevant:
> http://www.sommarskog.se/arrays-in-sql.html
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

> Thanks to both of you for your replies. It helps me solve the problem.
Does
> the new SQL Server (2005?) have any better way to handle arrays than SQL
> Server 2000??

If you're brave enough to try the CLR, you can code stored procedures and
other modules using .NET languages. So I imagine there is a way to do it
yourself, but nothing native... there are still no such things as "arrays"
in relational data...

There is better XML handling, so you might be able to pass data structures
in that way, as well.

--
http://www.aspfaq.com/
(Reverse address to reply.)