TSQL Sending Multiple rows to a stored procedure

Question

I need to send multiple rows to a stored procedure that is written to receive one record at a time. I can use a cursor, but would prefer to see if there are other options.

Are there other options to send multiple rows to a stored procedure for execution, or is cursor the only way ?

Answer 1

If the stored procedure can be re-written, it may better be written to accept table-valued parameter instead of one row at a time. If the stored procedure must remain as it is now, then there is not many other choices besides a cursor (or some other loop processing) or there is probably also a possibility to make a dynamic script invoking this procedure for several rows of data and then execute generated script using sp_executeSQL procedure.

Answer 2

I have an article on my web site entitled How to Share Data Between Stored Procedures that disusses options in this space.