• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 8. Designing SQL Server Stored P... > Stored Procedures and Temporary Tabl...

Stored Procedures and Temporary Tables

SQL Server creates temporary tables in a special system database called TempDB. SQL Server creates TempDB each time it starts and destroys it each time it shuts down. SQL Server uses TempDB to house many temporary objects that it needs to run. You can use TempDB to share data between procedures or to help you accomplish complex tasks. Many times you will need to incorporate temporary tables into the stored procedures that you write. Here's an example of how you create and use a temporary table:

CREATE PROCEDURE procCustomersGetTemp AS
BEGIN
CREATE TABLE #TempCustomers
(CompanyName varchar (50) NOT NULL PRIMARY KEY,
ContactName varchar(50),
ContactTitle varchar (50),
City varchar (50))
INSERT INTO #TempCustomers
(CompanyName, ContactName, ContactTitle, City)
EXEC procCustomersGetByCountryAndTitle 'USA', 'Owner'
SELECT CompanyName, ContactName, ContactTitle, City
FROM #TempCustomers
ORDER BY City, CompanyName
END


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint