Temp tables in dynamic sql

Text version of the video
http://csharp-video-tutorials.blogspo...

Slides
http://csharp-video-tutorials.blogspo...

All SQL Server Text Articles
http://csharp-video-tutorials.blogspo...

All SQL Server Slides
http://csharp-video-tutorials.blogspo...

All SQL Server Tutorial Videos
https://www.youtube.com/playlist?list...

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenka...

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

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

Temp tables in dynamic sql asp.net core docker
176 Likes	176 Dislikes
33,533 views views	524K followers
Science & Technology	Upload TimePublished on 9 May 2017

Related keywords

wcf vs web api,ado.net core,sql server management studio,webkinz,webadvisor,craigslist nj,wcf one piece,asp.net core 3,ajax players,weber grill parts,webtoon,tutorials by hugo,csharp corner,mvc design pattern,asp.net machine account,servers for minecraft,asp.net core dependency injection,ado.net tutorial,services angular,ajax ontario,asp.net mvc tutorial,asp.net cos'è,csharp assembly,tutorialspoint python,sql join,services briefcase,asp.net core web api,ajax jquery,wccftech,craigslist ny,asp.net zero,csharp foreach,server memes,sql date format,services online,chase,serverless architecture,server resume,wcf c#,server books,tutorialspoint javascript,mvcc connect,ado.net mysql,services technologies gps,ajax deadpool,server jobs,cvs,website,mvc tutorial,costco hours,wcf service application,tutorialspoint spring,serverless,wcf soap,wcf cat,wcf test client,cool math games,services & training hse colombia sas,servicestack,citibank,asp.net core identity,sql union,ajax parking,sql database,asp.net core logging,mvconnect,asp.net guida,cunyfirst,wcf nba,csharp download,wcfi foundation,csharp online,wcf authentication,tutorials near me,http://asp.net,server 2019,ado.net descargar,web of dreams,chernobyl,serverminer,ajax cleaner,ado.net visual studio 2019,webassign,ado.net vs entity framework,ado.net visual studio 2017,csharp list,sql like,asp.net mvc,asp.net core tutorial,sqlite,wcfm,ajax roster,mvc architecture,http://ado.net,asp.net core mvc,ajax soccer,server hosting,wcfi,ajax dish soap,capital one,server rack,tutorialspoint html,csharp interface,craigslist,webroot,tutorialspoint reactjs,ajax request,wcf dragon ball,asp.net core 2.2,tutorialspoint python 3,sql developer,webster,services transmission company sas,sql group by,asp.net core signalr,services manager,mvc framework,ajax paving,mvc near me,tutorialspoint spring boot,mvc map,csharp online compiler,asp.net download,sql between,ado.net c# pdf,wcf 2019 nba,services tag dell,csharp switch,ado.net ventajas y desventajas,csharpstar,wcf tutorial,tutorialspoint,ajax meaning,csharp-video-tutorials.blogspot,tutorials dojo,csharp string format,central park 5,ado.net c#,asp.net core github,server status,ajax fc,server jobs nyc,asp.net core swagger,sql formatter,credit karma,services group,server error in '/' application,services windows,asp.net core 3.0,sql injection,wcf ria services,tutorialspoint c#,calculator,ado.net entity data model,sql insert,tutorialspoint tableau,services google play apk,sqlyog,asp.net core 3 release date,sql server,server job description,tutorials by a,servicenow,webcam,mvc hours,webmd symptom,csharp array,csharp enum,ajax call,asp.net core 2. guida completa per lo sviluppatore,asp.net core,server pro,server status ffxiv,webcrims,cheap flights,asp.net core hosting,services sas,tutorialspoint java,tutorialspoint java compiler,webmd,csharp to json,college football,ado.net dataset,csharp dictionary,cnn,website builder,tutorialspoint sql,asp.net web api,server side rendering,weber grills,sql server 2017,mvc nj,tutorialspoint spark,ado.net oracle,asp.net core download,csharp to vb.net,webster bank,webstaurant,tutorialsystems,ajax post,services fms publish announcement,services.msc no abre,ajax jersey,csharp operator,asp.net core razor pages,server duties,asp.net core environment variables,csharp random,century 21,services consultores,services consulting,mvcsd,mvcsc,services.msc,ado.net pdf,asp.net core configuration,ajax marvel,sql update,asp.net tutorial,mvc medical,ado.net entity data model visual studio 2019,wcfs international curriculum,mvc2,ado.net entity data model visual studio 2017,chase online,wcf api,costco,server jobs near me,webex,sql meaning,sql tutorial,sql commands,ado.net entity framework,ajax tavern,tutorialsteacher,ajax javascript,services desk,ajax greek,csharp tutorial,mvc pattern,ado.net sql server,ado.net connection,asp.net identity,mvcu,asp.net core middleware,wcf web service,mvc webadvisor,web store,mvcc,webmail,mvci,mvctc,