How to use Temporary table in Stored procedure with example

In this article I'm going to explain How to use Temporary table in Stored procedure with example and also I'm explain what is Temporary table and its types.

Temporary table are create at run time and then you can do the all kind of operation like normal table.
Temporary table will be deleted when the current client session terminates. By default all the temporary tables are deleted by SQL when your database connection gets terminated. If you want to drop temp table manually then you need to write query like this drop table "temptablename". There are two types are temporary tables

1)Local Temp Table

2)Global Temp table.

Local temp table are available to the current connection for the user and this table will be deleted automatically when user disconnect from instances. Local temp table start with # sign.

Global temp table start with ## sign. This table available to any user by any connection. its can be deleted once all the connections are closed. Herewith i have give one example for Temporary tables.

CREATE PROCEDURE SPName
(
    @Parameter VARCHAR(100)
)
AS
BEGIN
    create table #TEmp_Table  
    (
        name Nvarchar(250),
        objectid int
    )
    INSERT INTO #TEmp_Table  
    Select name,object_id  from sys.tables where name =@Parameter
    
    
    select * from #TEmp_Table
    drop  table #TEmp_Table
END
 
 

 EXEC SPName 'DPI1'