Temporary table lifetime and SQLCLR stored procedures

People are sometimes concerned with the lifetime of temporary tables they create in CLR code. As an example, if I use a parameterized statement in .NET code, the SqlClient provider wraps the statement in exec sp_executesql. If the statement batch includes something like "CREATE TABLE #temp1" as part of the batch, the table #temp1 is no longer visible when the batch returns. This is because the lifetime of a temporary table created inside of a stored procedure is the stored procedure itself, ie. the temporary table no longer exists once the stored procedure completes. In this case, the stored procedure that's scoping the temp table is "sp_executesql". But… if proc A creates a temporary table and then (inside proc A) calls proc B that reads it, that's fine. Because nested procs can see "temp" tables created within outer scopes.

But what about procs written in SQLCLR? If you're using the context connection in your SQLCLR proc, visibility in the inner proc works fine, regardless of whether:
procs A and B are written in T-SQL
procs A and B are written in SQLCLR
proc A is SQLCLR and B is T-SQL
proc A is T-SQL and B is SQLCLR

Hope that clarifies things…

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.