在 SQL Server 2008 中的存储过程之间使用临时表
我目前有一个主存储过程调用许多存储过程：
Main --|
--|
--| > Exec Pre-processing SP (create and populate #temp table)
--|
--| > Exec Actual update
--|
--| > Exec Post-Process (consume and drop #temp table)
我当前遇到的问题是我需要从数据库中获取原始值，并将这些值传递到主存储过程中，以便我可以执行后处理调用最后一步（存储过程）中的更新。
我无法更改存储过程签名或主存储过程。
当我尝试执行此场景时，我在后处理存储过程中得到一个无效对象。
如何将我的值从第一个存储过程传递到最后一个存储过程。
I currently have a main stored procedure calling many stored procedures:
Main --|
--|
--| > Exec Pre-processing SP (create and populate #temp table)
--|
--| > Exec Actual update
--|
--| > Exec Post-Process (consume and drop #temp table)
The problem I'm currently having is that I need to grab the original values from the database, and the values being passed into the main stored procedure so that I can execute post processing to the update in the last step (stored procedure) being called.
I cannot change the stored procedure signatures or the main stored procedure.
When I try to execute this scenario, I get an invalid object in the post-processing stored procedure.
How can I pass my values from the first stored procedure to the last stored procedure.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您需要在调用层次结构中创建比使用它的所有存储过程更高的临时表。既然您说无法更改
Main，那么执行此操作的唯一机会是创建一个新的最外层过程，该过程创建#temp，然后调用Main>。然后，您可以修改预处理过程来填充已经存在的表。您不需要显式地将表删除到任何地方。
或者，您可以创建一个永久表，并包含一个 spid 列，该列指示从哪个连接插入数据。您还需要确保针对此表的任何选择/删除再次使用
@@SPID值。我将此发布为会话全局临时表You need to create the temp table higher up in the calling hierarchy than all the stored procs that consume it. Since you say you cannot alter
Main, the only chance to do this would be to create a new outermost procedure that creates#tempand then callsMain.You can then modify the pre- procedure to populate the already existing table. You shouldn't need to explicitly drop the table anywhere.
Alternatively, you could create a permanent table, and include a spid column that indicates which connection data was inserted from. You'd also ensure that any selects/deletes against this table again use the
@@SPIDvalue. I posted this as an answer to Session-global temporary tables如果
main过程始终在单个线程上运行，并且永远不会从多个连接并行调用，则可以将临时表声明为全局临时表 (##temp与#temp相反），假设您能够更改内部过程。如果同时从多个连接调用
main，这将不起作用。If the
mainprocedure is always run on a single thread and will never be called in parallel from multiple connections, you could declare your temporary table as a global temp table (##tempas opposed to#temp), assuming you are able to change the inner procedure.This won't work if
mainis called from several connections at the same time.