访问TSQL从CLR存储过程创建了#temp表。可能吗？

I have a TSQL Stored Procedure tsql__sp__A which does two things:

我有一个TSQL存储过程tsql__sp__A,它做了两件事:

(a) Creates a temp table #tempTable that has SELECT data from a complex SELECT query.

(a)创建一个临时表#tempTable,它具有来自复杂SELECT查询的SELECT数据。

(b) Calls a CLR managed Stored Procedure clr__sp__B for each row that does computation on row parameters.

(b)为行参数计算的每一行调用CLR管理的存储过程clr__sp__B。

Question: Is it possible to access #tempTable from CLR procedure clr__sp__B using the same connection context? (No, I don't want to move or create another #tempTable inside managed procedure)

问题:是否可以使用相同的连接上下文从CLR过程clr__sp__B访问#tempTable? (不,我不想在托管程序中移动或创建另一个#tempTable)

Thanks.

2 个解决方案

#1

Thank you Boj.

谢谢Boj。

However I found that when you use with a "context connections=true" it opens up all the SET

但是我发现当你使用“context connections = true”时,它会打开所有的SET

Read Bol Article

阅读Bol文章

//The context connection lets you execute SQL statements in the same context that your code was invoked in the first place//

//上下文连接允许您在与第一个调用代码相同的上下文中执行SQL语句//

using (SqlConnection connection = new SqlConnection("context connection=true"))
{
    connection.Open();
    // access #temp table
}

#2

We can define two types of temp tables in SQL.

我们可以在SQL中定义两种类型的临时表。

local
global

About local temp tables:

关于本地临时表:

When table is preceded by single ‘#’ sign, it is defined as local temporary table and its scope is limited to session in which it is created.

当表前面有单个“#”符号时,它被定义为本地临时表,其范围仅限于创建它的会话。

And about global temp tables:

关于全局临时表:

In contrast of local temporary tables, global temporary tables are visible across entire instance.

与本地临时表相比,整个实例中可以看到全局临时表。

So may you should try using "##" to create a global temp table. (If there is a difference between "connection context" and "session")

所以你可以尝试使用“##”来创建一个全局临时表。 (如果“连接上下文”和“会话”之间存在差异)

#1