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 ?
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.
Thank you, I will review the sp_executesql procedure. I'm thinking I will need to do the cursor. I could use a trigger perhaps on a temp table and then execute the stored procedure using sp_executesql, but I'm thinking I may still miss rows if they are inserted into the temp table at the same time.
Can't re-write the stored procedure - it is a canned sp.
I have an article on my web site entitled How to Share Data Between Stored Procedures that disusses options in this space.
16 people are following this question.
