question

vsslasd-3371 avatar image
0 Votes"
vsslasd-3371 asked vsslasd-3371 commented

TSQL Sending Multiple rows to a stored procedure

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 ?

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NaomiNNN avatar image
0 Votes"
NaomiNNN answered vsslasd-3371 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered vsslasd-3371 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you!

0 Votes 0 ·