SqlParamater updating values in a loop - side effects

Question

I have a stored proc that takes one integer parameter.

I am calling this in a loop (I would rather use an In but Stored proc is outwith my control).

So I have one SqlParameter object who's value is updated each time round a loop.

I have a vague memory of there being a side effect to this? Does this ring any bells?

(.NET 2.0 / SQL Server 2005)

Answer 1

If the code is reasonably straightforward and you're just updating the parameter's value on each iteration then I'd consider this good practice.

There aren't any side effects that I'm aware of.

Answer 2

The main side effect that comes to mind is poor performance.

If you can do the loop on the server side instead (best), or at least combine multiple statements into a single batch, you will be much better off.