CODEWITHSUNDEEP
.netasp.netvb.netstored-proceduresdynamic
Madam Zu Zu
Madam Zu Zu

Reputation: 6605

Dynamic parameters in a Stored Procedure?

I need to update a table with values from a listbox. I am trying to create as many parameters as there are items in the list, my code below, but how do i name them differently? so that they dont' overwrite each other?

 For Each item As ListItem In ris
        Dim pID As New SqlParameter("@userid", SqlDbType.Int)
        pID.Value = objFormat.CheckSQL(item.Value)
        myCommand.Parameters.Add(pID)
    Next

and on the SQL side, is it possible to write an update statement, that will take a dynamic number of parameters?

so for example, i need to update multiple users with the same value in the "active" field...

Upvotes: 0

Views: 1087

Answers (3)

XIVSolutions
XIVSolutions

Reputation: 4502

re: "thanks, is it bad to loop through the list items, and call the stored procedure that many times? instead of doing it all in one stored procedure? – xrum Jan 19 at 16:55 "

If you think about it, you're going to be doing some iterating somewhere - why not load all of your values into a list, and perform the updates as you describe? If it were me, I would just make sure I was only opening the Connection once:

    Dim CommandText As String = _
    "UPDATE MyTYable SET SomeField = @SomeField " & _
    "WHERE UserID = @UserID"

    Using cn As New SqlClient.SqlConnection(MyConnectionString)
        Using cmd As New SqlClient.SqlCommand(cn, CommandText)

            'The Record key in the database:
            cmd.Parameters.AddWithValue("@UserID", -1)

            'Whatever it is you plan to UPDATE:
            cmd.Parameters.Add("@SomeField", "")

            cn.Open()

            For Each Item As ListItem In ris
                cmd.Parameters("@UserID").Value = Item.Value
                cmd.Parameters("SomeField").Value = SomeValueFromSomeWhere
                cmd.ExecuteNonQuery()
            Next

        End Using
    End Using

Obviously, this will need to be tuned a little to your particular useage. I am not certain what you are doing with your assembled list of UserID's, but the code skeleton above represents a relatively efficient means of performing a series of INSERTS or UPDATES without incurring the overhead of opening and closing the overall connection.

If the number of updates you need to perform is truly onerous, one of the string concatenation methods mentioned above might be more efficient, and/or the rapid insertion of all the values to be UPDATED into a Temp Table on the backside Db, followed by a SELECT INTO statement which handles the bulk INSERT on the server.

Upvotes: 0

LukeH
LukeH

Reputation: 269328

Upvotes: 1

DarinH
DarinH

Reputation: 4879

Here's one take on it, basically pass the "list" in a single string param, seperated by commas (or whatever) and the parse it in the sproc.

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

there's also this

Since there is no Sqlserver array parameter, what's the best way to proceed?

Upvotes: 1

Related Questions