- Temp Tables
- Store Procedures
- Passing Variables as Parameters to Store Procedure
My job is heavily driven on the Elite Enterprise Database and Metastorm Database, but in this example I will use the Adventure Works database supplied with SQL Server 2008. First thing I had to do in the store procedure is create the temp table which is shown below. The greatest thing about a temp table is the fact it is auto deleted after the connection to the database has ended.
CREATE TABLE #productsubcategory
(
productsubcategoryid INT,
productcategoryid INT,
name NVARCHAR(50),
modifieddate DATETIME
)
After I created the table I need to insert the values in to that table. The following is my insert statement.
INSERT INTO #productsubcategory
SELECT productsubcategoryid,
productcategoryid,
name,
modifieddate
FROM production.productsubcategory
Now I just have to be able to return the values. I create a simple SQL statement with a join with the parameters in to the where clause.
SELECT productsubcategoryid,
production.productcategory.productcategoryid,
tempsubcategory.name AS 'Subcategory Name',
production.productcategory.name AS 'Category Name',
tempsubcategory.modifieddate
FROM #productsubcategory AS tempsubcategory
INNER JOIN production.productcategory
ON productcategory.productcategoryid =
tempsubcategory.productcategoryid
WHERE tempsubcategory.modifieddate >= @Start_Date
AND tempsubcategory.modifieddate <= @End_Date
ORDER BY [Subcategory Name]
Below is my entire Store Procedure written out so you can see the entire statement in one piece instead of multi statements.
CREATE PROCEDURE Sp_parameters
-- Add the parameters for the stored procedure here
@Start_Date DATETIME,
@End_Date DATETIME
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET nocount ON;
-- Insert statements for procedure here
--Creating the temp table to hold all the different product sub category
CREATE TABLE #productsubcategory
(
productsubcategoryid INT,
productcategoryid INT,
name NVARCHAR(50),
modifieddate DATETIME
)
--inserting values into table
INSERT INTO #productsubcategory
SELECT productsubcategoryid,
productcategoryid,
name,
modifieddate
FROM production.productsubcategory
--returning the values
SELECT productsubcategoryid,
production.productcategory.productcategoryid,
tempsubcategory.name AS 'Subcategory Name',
production.productcategory.name AS 'Category Name',
tempsubcategory.modifieddate
FROM #productsubcategory AS tempsubcategory
INNER JOIN production.productcategory
ON productcategory.productcategoryid =
tempsubcategory.productcategoryid
WHERE tempsubcategory.modifieddate >= @Start_Date
AND tempsubcategory.modifieddate <= @End_Date
ORDER BY [Subcategory Name]
END
GO
Now how do we call that Store Procedure to get the values we need? Simple. Below is the way I called the Store Procedure.
USE adventureworks
DECLARE @Start_Date DATETIME
DECLARE @End_Date DATETIME
SET @Start_Date = '1998-06-01';
SET @End_Date = '1998-06-01';
EXECUTE Sp_parameters
@Start_Date,
@End_Date
As you can see in this example is not very complex. In my small project at work it was a bit more complex. I had a few more temp tables as well as various joins such as inner, left and cross joints. But you can see how effective temp tables and Store Procedures can be in SQL.
I hope you found this blog helpful and thanks again for visiting.
Robert
