Temporary Table in Stored Procedure with Inner Join in SQL Server

Last Reply 8 months ago By pandeyism

Posted 8 months ago

What is the role or use of temporary table in stored procedure when using with inner join ?

can anyone provide me the sample.

 

Posted 8 months ago Modified on 8 months ago

Hey chetan,

The role of temporary table is you can create and use temporary tables in a stored procedure, but the temporary table exists only for the duration of the stored procedure that creates it. When the procedure completes, Adaptive Server automatically drops the temporary table.

SQL

CREATE PROCEDURE TestProcedureJoin 
AS  
BEGIN  
CREATE TABLE #Test1(Id INT, Name VARCHAR(20))  
INSERT INTO #Test1 VALUES(1, 'Mudassar')  
INSERT INTO #Test1 VALUES(2, 'John')  
INSERT INTO #Test1 VALUES(3, 'Suzanne')

Create Table #Test2(Id INT, Country VARCHAR(20))  
INSERT INTO #Test2 VALUES(1, 'India')  
INSERT INTO #Test2 VALUES(2, 'USA')  
INSERT INTO #Test2 VALUES(3, 'Russia') 
   
SELECT t1.Id, t1.Name, t2.Country FROM #Test1 t1
INNER JOIN #Test2 t2 ON t1.Id =t2.Id

DROP TABLE #Test1
DROP TABLE #Test2
End
GO
EXEC TestProcedureJoin

Screenshot

Id Name Country
1 Mudassar India
2 John USA
3 Suzanne Russia