Local and global temp tables inside and out of stored procedures

By: Rick Dobson

Overview

In this part of the tutorial, we look at how temp tables can be accessed when used in a stored procedure and the differences of local versus global temp tables.

Local and global temp tables inside and out of stored procedures

Even within the same tab, global and local temp tables can operate differently.  A stored procedure is a well-known example of how the two types of temp tables operate differently.  Local temp tables created in a stored procedure within a connection cannot be referenced outside of the stored procedure.  However, global temp tables created inside a stored procedure can be referenced from outside the stored procedure.

The following script demonstrates how references to local temp tables and global temp tables operate when the temp table is created in a stored procedure, but it is referenced outside the stored procedure.  Notice that the for_local_and_global_temp_tables_demo stored procedure creates two temp tables.  One table named #married_male_from_proc is a local temp table.  Another table named ##not_married_from_proc is a global temp table.

The script creates the for_local_and_global_temp_tables_demo stored procedure in the Temporary _Data_Stores_Tutorial database with a create proc statement.  The stored procedure is very simple.  One select into statement creates and populates #married_male_from_proc, a local temp table.  Another select into statement creates and populates ##not_married_from_proc, a global temp table.

After the create proc statement, an exec statement invokes the for_local_and_global_temp_tables_demo stored procedure in its own batch.

Next, a select statement in its own batch attempts to perform a select statement on the local temp table created in the stored procedure.  Even though local temp table created in the stored procedure is from the same tab as the select statement querying the temp table outside the stored procedure, the query fails.  The Messages tab shows the error is for an invalid object name.  This follows from the fact that the local table name, #not_married_from_proc, has a scope that is restricted to the stored procedure.

On the other hand, the select statement for the global temp table named ##not_married_from_proc succeeds outside the stored procedure.  The global temp table is not restricted to a scope inside the stored proc.  It returns a count of the employees who are not married.  This count is validated by another query that runs directly against the Employee table in the HumanResources schema of the AdventureWorks2014 database.

USE [Temporary_Data_Stores_Tutorial]
GO

-- this is to drop a local table
BEGIN TRY
  DROP TABLE #married_male_from_proc
END TRY
BEGIN CATCH
  PRINT '#married_male_from_proc not available to drop'
END CATCH

-- this is to drop a global temp table
BEGIN TRY
  DROP TABLE ##not_married_from_proc
END TRY
BEGIN CATCH
  PRINT '##not_married_from_proc not available to drop'
END CATCH

BEGIN TRY
  DROP PROC for_local_and_global_temp_tables_demo
END TRY
BEGIN CATCH
  PRINT 'for_local_and_global_temp_tables_demo stored proc not available to drop'
END CATCH
GO

CREATE PROC for_local_and_global_temp_tables_demo
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  -- Insert statements for procedure here
  -- populate local temp table for married, male employees
  SELECT
    [BusinessEntityID] INTO #married_male_from_proc
  FROM [AdventureWorks2014].[HumanResources].[Employee]
  WHERE [MaritalStatus] = 'M'
  AND gender = 'M'

  -- populate global temp table for not married employees
  SELECT
    [BusinessEntityID] INTO ##not_married_from_proc
  FROM [AdventureWorks2014].[HumanResources].[Employee]
  WHERE [MaritalStatus] != 'M'
END
GO

-- execute the stored procedure to create
-- local and global temp tables
EXEC for_local_and_global_temp_tables_demo
GO

-- attempt to display local temp table
-- original created in a stored proc
SELECT
  COUNT(*)
FROM #married_male_from_proc
GO

-- attempt to display global temp table
-- original created in a stored proc
SELECT
  COUNT(*) count_of_not_married_from_global_temp_table
FROM ##not_married_from_proc

-- not married employees from Employee table
-- in HumanResources schema
-- of the AdventureWorks2014 database
SELECT
  COUNT(*) count_of_not_married_from_Employee_table
FROM [AdventureWorks2014].[HumanResources].[Employee]
WHERE MaritalStatus != 'M'
GO

Additional Information

Here are some links to resources that you may find useful to help you grow your understanding of content from this section of the tutorial.

• AdventureWorks sample databases
• Install Your Own Copy of the SQL Server AdventureWorks2014 Database
• Differences between SQL Server temporary tables and table variables
• Introduction to Temporary Tables in SQL Server
• Temporary Tables in SQL Server
• SQL Server Temporary Tables
• How to get a list of global temporary tables in the current database?
• Is there a way to get a list of all current temporary tables in SQL Server?
• tempdb database in Parallel Data Warehouse