Array as variable ?

Hi,
I am in a situation where I have an array of userIDs in VB .Net. For each of
these UserIDs I need to query the database for the fastest time for each
user and would like to fill a dataset ordered with fastest to slowest time.
Is there any way I can manage this in T-SQL and submit an array of userIDs
as a variable to the stored procedure, or do I need to manage this in my
code by submitting one query per user and do the sorting etc in code ?
Niclashttp://www.sommarskog.se/arrays-in-sql.html|||To add to Omnibuzz's response, you can also consider using the XML data type
if you're using SQL 2005.
Hope this helps.
Dan Guzman
SQL Server MVP
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:OOAMYC4aGHA.4788@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am in a situation where I have an array of userIDs in VB .Net. For each
> of these UserIDs I need to query the database for the fastest time for
> each user and would like to fill a dataset ordered with fastest to slowest
> time.
> Is there any way I can manage this in T-SQL and submit an array of userIDs
> as a variable to the stored procedure, or do I need to manage this in my
> code by submitting one query per user and do the sorting etc in code ?
> Niclas
>|||> I am in a situation where I have an array of userIDs in VB .Net. For each
> of these UserIDs I need to query the database for the fastest time for
> each user and would like to fill a dataset ordered with fastest to slowest
> time.
> Is there any way I can manage this in T-SQL and submit an array of userIDs
> as a variable to the stored procedure, or do I need to manage this in my
> code by submitting one query per user and do the sorting etc in code ?
The simplest way of dealing with an array of updates / inserts / deletes in
.NET is to either (a) iterate over that array, passing each value one at a
time to your stored procedure, which is pretty inefficient, or to (b) slurp
the data up into a DataTable or DataSet and do something like this:
If you want to see it in C#, just say so, and I will repost:
Imports Jedi.Mind.Trick
' you're putting your connection strings in your .config file, aren't you?!?
Dim cnstr As String = ConfigurationSettings.AppSettings("connectionString")
Dim cn As New SqlConnection(cnstr)
' SQL Statement or stored procedure that updates a *single* record
Dim cm As New SqlConnection("INSERT INTO ... VALUES (...)", cn)
Dim da As New SqlDataAdapter(cm)
' whatever you need to hold your *multiple records* of data.
Dim dt As New DataTable() ' or, if you prefer, Dim ds As New DataSet()
' get your data from wherever
da.Update(dt) ' does inserts, updates, and deletes, as neccesary
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei