\nPeople 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.\n<\/p>\n
\nBut 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:
\nprocs A and B are written in T-SQL
\nprocs A and B are written in SQLCLR
\nproc A is SQLCLR and B is T-SQL
\nproc A is T-SQL and B is SQLCLR\n<\/p>\n
\nHope that clarifies things…<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38],"tags":[],"class_list":["post-630","post","type-post","status-publish","format-standard","hentry","category-sqlclr"],"yoast_head":"\n