Temp tables in dynamic sql

Text version of the video

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.

Slides

All SQL Server Text Articles

All SQL Server Slides

All SQL Server Tutorial Videos

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

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

[krupapatel]

Just completed all 150 videos series. Learn so much new things eventhough I am not begginner.

This series is STILL relavent in 2023 and many more years to come!!

Thanks Venkat!

[rushikeshkarche]

Finally i Watched 150 videos , Thnks for your Good work....


Thanks venkat

[someone.user]

Thanks Sir, watched whole series from video 1 to video 150.
I am data engineer and these videos are very helpfull.
Thanks a lot

[ronaldespinoquispe]

For a "beginner's SQL Server tutorial" it has been too thorough. Thanks!

[Trzbne]

Dear Venkat,

I watched the total SQL tutorial series. You are a wonderful teacher.

We must aknowledge your hard work, the flawless videos. You are talking and showing the solutions without hesitation, in good pace. It is clear, that you are well prepared for all of your videos. Your voice calms me down and make me focused. You are the voice of learning!

Thank you very much. God bless you. Receive back all the good things you do to other people!

[abidtaqi]

Ahh finished 150 videos!

Thanks a lot to Venkat for your time & efforts. Apart from 4-5 videos I understood everything in this tutorial. Though I didn't practice all the codes myself so I might need to come back to these videos again when required 😂.

[abdullahmohammad]

Thanks for your outstanding effort!

Best SQL instructor ever!!

[naodagere]

Thank you so much Venkat! I hope your good work is helping millions. Definitely you will harvest the fruits of your good works. God bless you.

[bmrpdlw]

That is probablym the best guide on dbs, I`ve ever meet. May the force be with you, Venkat!

[itspraveenkp]

Finally Done all videos.... thanks a lot for make a tutorials.

[nys]

Thank You venkat
i watched all 149 + 1 videos
you are the best teacher on youtube

[nezarammari]

The Best SQL tutorial channel on the web, Thank you so much and could you please make a video about the new updates in SQL since 2016 ... Thank you

[adityagurram]

Hi Venkat. Watched all the 149 videos. They are too good and thank you much sir.

[abhinavbhardwaj]

Watched all 150 videos .
I would recommend this to all who wants to learn SQL

[mattaku]

Yeeeeah, i foooking did it, let's go, all 150 videos in a week.
You're a great teacher. It would be great if you add to this one to many, many to many relationships and show how to make them, but besides of that it's a really great course.

[Jaszczer]

Great content! I watched most of the videos, that helped me upgrade my sql knowledge, especially in term of sql stored procedures and dynamic sql. Thanks a lot! I am looking forward to the next videos :)

[BigDataBrew]

Thanks Sir, I got placed at 5 lacks pa by watching your videos..thanks for your effort

[srividya]

Venkat i have completed all your videos...believe me because of ur videos i have learnt from scratch these are the only videos that every thing explained in a fantastic manner..i request you to make videos on azure data factory.. i am looking forward to hear from you..tq u venkat...

[zakiabdullahimohamed]

Finally i Watched 150 videos , Thnks for your Good work

[kaviyarasu_Official]

Thank you so much for the great tutorial series, i am learned lot of things.