Accessing TSQL created #temp tables from CLR stored procedure. Is it possible?

Question

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

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

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

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)

Thanks.

Answer 1

We can define two types of temp tables in 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")

Answer 2

Thank you Boj.

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

Read Bol Article

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

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