Reputation: 1463
I have this query:
s="update myTable set date='09/09/12' where crc32 in("+list+")";
But because of my list is with thousands of values I think that there is a better way to do it. I don't know which way, or how.
Update: The column crc32
is int
. The list
is string like:
list = "2345,-6467,7865,7654,...."
I exeute the query by c#. the list
contains values that I cann't get them by sql query, just through my code.
Thanks
Upvotes: 1
Views: 538
Reputation: 7267
Check Table valued parameters. http://msdn.microsoft.com/en-us/library/bb510489.aspx Put it in a stored procedure, like:
create procedure mysp
@tvp tvp_list readonly
as
update mt
set date='09/09/12'
from mytable mt
join @tvp t on tvp.id = crc32
And call it from C# with a datatable parameter containing your crc32
var cmd = new SqlCommand(CommandText = "prc_update",conn);
...
var dt = new DataTable();
dt.Columns.Add("crc32",typeof(string));
..... you can add more if you like
foreach(var item in myList)
{
var row = dt.NewRow();
row["crc32"] = item;
dt.Rows.Add(row);
}
cmd.Parameters.Add("@tvp", System.Data.SqlDbType.Structured);
cmd.Parameters["@tvp"].Value = dt;
.....
conn.Open();
cmd.ExecuteNonQuery();
Upvotes: 2
Reputation: 1491
Is List
in the database somewhere?
As an UPDATE
with a JOIN
is probably quicker, it might even be quicker to create a table, put List
in it, and then do the UPDATE
with a JOIN
.
Upvotes: 1