SQL Server 中的会话全局临时表 [英] Session-global temporary tables in SQL Server

问题描述

在 SQL Server 中，名为 #temp 的临时表具有本地作用域.如果您在会话中创建它们，会话中的所有内容都可以看到它们，但不能在会话之外看到.如果在存储过程中创建这样的表，则作用域是该过程的本地范围.因此，当 proc 退出时，表就消失了.

In SQL Server, temporary tables with a name like #temp has a local scope. If you create them in your session, everything in your session can see them, but not outside your session. If you create such a table within a stored procedure, the scope is local to that procedure. So when the proc exits, the table vanishes.

我所知道的唯一替代方法是使用名称类似于##temp 的表.这些是临时的，但在服务器范围内可见.因此，如果我在会话中创建该表，隔壁办公室的 Bob 也会看到它们.

The only alternative I am aware of, is to use tables with a name like ##temp. These are temporary, but are visible server-wide. So if I create the table in my session, Bob in the office next door will also see them.

我正在寻找的是中间的某个地方，因此我可以在存储过程中创建表，并且即使在存储过程退出后，该表也可用于我的会话.我能找到的最近的方法是创建一个只有一个字段的表，然后在存储过程中更改它.不过，这似乎有点麻烦.

What I am looking for is somewhere in the middle, so I can create the table within a stored procedure and have that table be available to my session even after the stored proc exits. The nearest I have been able to find, is to create the table with only one field on it and then alter it within the stored proc. That seems like a bit of a kludge, though.

推荐答案

难道你开始会话时不创建表，然后执行存储过程，然后在存储过程之后对表做任何你想做的事情执行?

Could you not create the table when you start the session, then execute the stored proc, then do whatever else you want to do to the table after the stored proc executes?

这篇关于SQL Server 中的会话全局临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！