SQL Server - Stored Procedure on an Aggregate Query

Create Stored Procedure

It is easy to create a stored procedure out of an aggregate select statement. By doing this you have the ability to pass in parameters at runtime.

IF ( OBJECT_ID('dbo.spSalesByProductSel') IS NOT NULL ) 
   DROP PROCEDURE dbo.spSalesByProductSel
GO

CREATE PROCEDURE dbo.spSalesByProductSel
       @OrderBeginDate        INT   ,
       @OrderEndDate          INT         
AS
BEGIN 
     SET NOCOUNT ON 

SELECT     
 DimProductCategory.EnglishProductCategoryName AS "Category",
 DimProductSubcategory.EnglishProductSubcategoryName AS "Subcategory",
 DimProduct.EnglishProductName AS "Product",
 SUM(FactInternetSales.OrderQuantity) AS "Tot Qty", 
  SUM(FactInternetSales.SalesAmount) AS "Tot Sales"
FROM    DimProduct 
INNER JOIN DimProductSubcategory ON 
DimProduct.ProductSubcategoryKey = 
DimProductSubcategory.ProductSubcategoryKey 
INNER JOIN DimProductCategory ON 
DimProductSubcategory.ProductCategoryKey = 
DimProductCategory.ProductCategoryKey 
INNER JOIN FactInternetSales ON 
DimProduct.ProductKey = FactInternetSales.ProductKey
WHERE OrderDateKey >= @OrderBeginDate AND OrderDateKey < @OrderEndDate
GROUP BY DimProductCategory.EnglishProductCategoryName, 
         DimProductSubcategory.EnglishProductSubcategoryName, 
         DimProduct.EnglishProductName   
ORDER BY DimProductCategory.EnglishProductCategoryName ASC,
         DimProductSubcategory.EnglishProductSubcategoryName ASC,
         DimProduct.EnglishProductName ASC   

END 
GO 

Execute Stored Procedure

EXEC dbo.spSalesByProductSel
     @OrderBeginDate   =  20070101  , 
     @OrderEndDate     =  20080000     
GO 


aggsp



Being able to specific a datetime range at runtime gives us a great deal of flexibility in our stored procedure. This is not always the case when you need to work with operators like > or <. There is no way to set operators at runtime within a stored procedure. If you need this degree of flexibility you are best served by creating a dynamic SQL query using T-SQL.



 Collapse All    Expand All
Custom Search

SQL Server Information and Resources