How are temporary tables used in stored procedure?

There are 2 types of temporary tables: local temporary table, global temporary table.

Local temporary table: This table can be used by only the session which has created the table. And it drops when the session is closed.

create Procedure usp_test_localTempTable

as

Begin

Create Table #Employ(EMP_Id int, EMP_Name nvarchar(50))

Insert into #Employ Values(1, 'EMP1')

Insert into #Employ Values(2, 'EMP2')

Insert into #Employ Values(3, 'EMP3')

select * from tempdb..#Employ

select * from tempdb.sys.tables where name like '#Employ%'

End

On execution usp_test_localTempTable

But when we execute the query outside the procedure we get the error for invalid object

Global temporary table: This table can be used by anyone. And is dropped at the when session which has created the table is closed. These kinds of tables can be used for the parent and child procedure in which the parent procedure creates the table and we can use in child procedure and the table will be dropped at the end of execution of the parent table. But this table needs to be used carefully if there are concurrent users as till the first table is not dropped it will not let the same table create with the same name.

Sample for creating a global table:

create Procedure usp_test_GlobalTempTable

as

Begin

Create Table ##Employ(EMP_Id int, EMP_Name nvarchar(50))

Insert into ##Employ Values(1, 'EMP1')

Insert into ##Employ Values(2, 'EMP2')

Insert into ##Employ Values(3, 'EMP3')

select * from tempdb..##Employ

select * from tempdb.sys.tables where name like '##Employ%'

End

On execution usp_test_GlobalTempTable

On re-execution we get the error as