Suggested Videos
Part 146 - Quotename function in SQL Server
Part 147 - Dynamic SQL vs Stored Procedure
Part 148 - Dynamic sql output parameter
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
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.
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.
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
Part 146 - Quotename function in SQL Server
Part 147 - Dynamic SQL vs Stored Procedure
Part 148 - Dynamic sql output parameter
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
- 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.
- On the other hand, dynamic SQL block can access temp tables created by the calling stored procedure
World is still beautiful because of some people like you.
ReplyDeleteUsing Global temp table , access out of scope is possible .
ReplyDeleteYep, For that we need to introduce ## in front of the table object name
DeleteThnx a LOT of TON .. i have completed My SQL - SERVER Tutorial - today @ 25th Oct 2023 --- 3 weeks back to back understanding each & every vedio made me more confident & interest towards the Subject .. Shorty i will be be completing ML ,DL , NLP & finally apply to my dream Job- Data Science .. I will practice a lot on SQL (Your vedios - revision) & apply for JOB
DeleteThnx
Siddhartha
Bangalore - India
yes it can be access by outside scope
ReplyDeleteset @query = 'create table ##Temp1(id int)
insert into ##Temp1(id) values(101)';
execute sp_executesql @query
select * from ##Temp1
Yes. it is working Using Global temp table , access out of scope is possible . Thanks a lot for the sharing knowledge.
ReplyDelete