Temp tables in dynamic sql

Text version of the video
http://csharp-video-tutorials.blogspot.com/2017/05/temp-tables-in-dynamic-sql.html

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1

Slides
http://csharp-video-tutorials.blogspot.com/2017/05/temp-tables-in-dynamic-sql_9.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

All SQL Server Tutorial Videos

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists

In this video we will discuss the implications of creating temp tables in dynamic sql

Temp tables created by dynamic SQL are not accessible from the calling procedure. They are dropped when the dynamic SQL block in the stored procedure completes execution.

Let us understand this with an example. Notice in the example below, all the following 3 operations are in the block of dynamic sql code.
1. Creating the Temp Table
2. Populating the Temp Table
3. Select query on the Temp Table

Create procedure spTempTableInDynamicSQL
as
Begin
Declare @sql nvarchar(max)
Set @sql = ‘Create Table #Test(Id int)
insert into #Test values (101)
Select * from #Test’
Execute sp_executesql @sql
End

So when we execute the above procedure we are able to access data from the Temp Table.
Execute spTempTableInDynamicSQL

Now, let’s move the SELECT statement outside of the dynamic sql code block as shown below and ALTER the stored procedure.

Alter procedure spTempTableInDynamicSQL
as
Begin
Declare @sql nvarchar(max)
Set @sql = ‘Create Table #Test(Id int)
insert into #Test values (101)’
Execute sp_executesql @sql
Select * from #Test
End

At this point, execute the stored procedure. Notice, we get the error – Invalid object name ‘#Test’. This is because temp tables created by dynamic SQL are not accessible from the calling procedure. They are dropped when the dynamic SQL block in the stored procedure completes execution.
Execute spTempTableInDynamicSQL

On the other hand, dynamic SQL block can access temp tables created by the calling stored procedure. Let’s prove this by modifying the stored procedure as shown below.

Alter procedure spTempTableInDynamicSQL
as
Begin
Create Table #Test(Id int)
insert into #Test values (101)
Declare @sql nvarchar(max)
Set @sql = ‘Select * from #Test’
Execute sp_executesql @sql
End

At this point, execute the stored procedure. Notice that we are able to access the temp table, which proves that dynamic SQL block can access temp tables created by the calling stored procedure.
Execute spTempTableInDynamicSQL

Summary
1. Temp tables created by dynamic SQL are not accessible from the calling procedure.
2. They are dropped when the dynamic SQL block in the stored procedure completes execution.
3. On the other hand, dynamic SQL block can access temp tables created by the calling stored procedure