如何将值从存储过程中的表参数传递到另一个存储过程?
我编写了一个名为 FooUpsert 的存储过程，用于在各种表中插入和更新数据.它需要一些提供数据的数字和字符串参数.这个程序很好，我不想修改它.
I've written a stored procedure called FooUpsert that inserts and updates data in various tables. It takes a number of numeric and string parameters that provide the data. This procedure is in good shape and I don't want to modify it.
接下来，我正在编写另一个作为批量插入/更新服务的存储过程.
Next, I'm writing another stored procedure that servers as a sort of bulk insert/update.
同样重要的是，该过程作为一个原子事务来完成它的工作.某些数据被插入/更新而某些数据不被插入/更新是不可接受的.
It is of tantamount importance that the procedure do its work as an atomic transaction. It would be unacceptable for some data to be inserted/updated and some not.
在我看来，这样做的合适方法是建立一个表值过程，例如 FooUpsertBulk.我开始用一个表参数来编写这个存储过程，它保存的数据类似于传递给 FooUpsert 的数据，这个想法是我可以一次读取一行并调用 FooUpsert代码> 用于每行中的值.我意识到这可能不是最佳实践，但再次重申，FooUpsert 已经编写完毕，而且 FooUpsertBulk 每天最多运行几次.
It seemed to me that the appropriate way of doing this would be to set up a table-valued procedure, say FooUpsertBulk. I began to write this stored procedure with a table parameter that holds data similar to what is passed to FooUpsert, the idea being that I can read it one row at a time and invoke FooUpsert for the values in each row. I realize that this may not be the best practice for this, but once again, FooUpsert is already written, plus FooUpsertBulk will be run at most a few times a day.
问题是在FooUpsertBulk 中，我不知道如何迭代行并将每行中的值作为参数传递给FooUpsert.我确实意识到我可以更改 FooUpsert 以接受表值参数，但我不想重写 FooUpsert.
The problem is that in FooUpsertBulk, I don't know how to iterate the rows and pass the values in each row as parameters to FooUpsert. I do realize that I could change FooUpsert to accept a table-values parameter as well, but I don't want to rewrite FooUpsert.
你们中的一位 SQL 忍者可以告诉我如何做到这一点吗?
Can one of you SQL ninjas out there please show me how to do this?
我的 SQL 服务器是 MS-SQL 2008.
My SQL server is MS-SQL 2008.
将各种查询包装成一个显式事务(即 BEGIN TRAN ... COMMIT 或 ROLLBACK) 使其成为原子操作.您可以:
Wrapping various queries into an explicit transaction (i.e. BEGIN TRAN ... COMMIT or ROLLBACK) makes all of it an atomic operation. You can:
从应用程序代码开始事务(假设
FooUpsert被应用程序代码调用)，因此也必须在那里处理提交和回滚.这仍然会留下许多小操作，但只需一个事务，无需更改代码.
start the transaction from the app code (assuming that
FooUpsertis called by app code) and hence have to deal with the commit and rollback there as well. this still leaves lots of small operations, but a single transaction and no code changes needed.
在 proc 中启动事务，在包含在 TRY/CATCH 中的循环中调用 FooUpsert，以便您可以在对 FooUpsert的任何调用时处理 ROLLBACK代码>失败.
start the transaction in a proc, calling FooUpsert in a loop that is contained in a TRY / CATCH so that you can handle the ROLLBACK if any call to FooUpsert fails.
将FooUpsert 中的代码复制到一个新的FooUpsertBulk 中，该FooUpsertBulk 接受来自应用代码的TVP 并将所有内容作为基于集合的操作进行处理.调整 FooUpsertBulk 中的每个查询，从处理各种输入参数到在 TVP 加入查询后从 TVP 表变量中获取字段.保持 FooUpsert 到位，直到 FooUpsertBulk 工作.
copy the code from FooUpsert into a new FooUpsertBulk that accepts a TVP from the app code and handles everything as set-based operations. Adapt each of the queries in FooUpsertBulk from handling various input params to getting fields from the TVP table variables once the TVP is joined into the query. Keep FooUpsert in place until FooUpsertBulk is working.