Stored Procedures and Temporary Tables

In Access, when you need to perform a sequence of complex operations to deliver a dataset for users to interact with on a form, you might need to create temporary results table, which you then clear down before populating with data with a sequence of queries. For example, in an order processing system, you might want to show past orders, payments, and future orders all in a single list. This involves consolidating data from many parts of the system.

When linking to SQL Server, you have a number of different design strategies from which to choose: you can keep using the existing local temporary tables, or you could put the temporary table in SQL Server and add a column to distinguish between data belonging to different users by using the SUSER_SNAME() function.

One elegant solution to this problem is to utilize SQL Server temporary tables inside a stored procedure; for example, you can create the following stored procedure (Stored ProcedureAndTemporaryTable.sql):

CREATE PROC usp_Products
AS
BEGIN
-- Drop temporary tables if they exists
IF object_id('tempdb..#TempProducts') is not null
    DROP TABLE #TempProducts

-- create the temporary results set
    SELECT * INTO #TempProducts
    FROM Products
-- return the temporary result set
    SELECT * from #TempProducts
END
GO
-- Testing
exec usp_Products
GO