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

Question

• I have a TSQL tsql__sp__A stored procedure that performs two functions:

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

(b) Calls the CLR stored procedure clr__sp__B for each row that performs calculations on the parameters of the row.

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

Thanks.

Answer 1

Thanks, Fight.

However, I found that when you use "contextual connections = true", it opens all SET

Read the article in Bol

// Contextual connection allows you to execute SQL statements in the same context in which your code was called first //

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

Answer 2

We can define two types of temporary tables in SQL.

• local
• global

About local temp tables:

When a single “#” sign precedes the table, it is defined as a local temporary table, and its scope is limited to the session in which it was created.

And about global temp tables:

Unlike local temporary tables, global temporary tables are visible throughout the instance.

, "##" temp. ( " " "" )