Database Reference
In-Depth Information
Listing 13-25 shows an example of a stored procedure that uses the code we have built so far.
Listing 13-25.
Creating a Stored Procedure
-- Using Stored Procedures
CREATE PROCEDURE pSelQuantitiesByTitleAndDate
(
-- 1) Define the parameter list:
-- Parameter Name, Data Type, Default Value --
@ShowAll nVarchar(4) = 'True' -- 'True|False'
, @StartDate datetime = '01/01/1990' -- 'Any valid date'
, @EndDate datetime = '01/01/2100' -- 'Any valid date'
, @Prefix nVarchar(3) = '%' -- 'Any three wildcard search characters'
--, @AverageQty int
--SELECT @AverageQty = Avg(SalesQuantity) FROM DWPubsSales.dbo.FactSales
)
AS
BEGIN -- the body of the stored procedure --
-- 2) Set the @AverageQty variable here since you cannot use subqueries in the
-- a stored procedures parameter list.
DECLARE @AverageQty int
SELECT @AverageQty = Avg(SalesQuantity) FROM DWPubsSales.dbo.FactSales
--3) Get the Report Data
SELECT
DP.PublisherName
, [Title] = DT.TitleName
, [TitleId] = DT.TitleId
, [OrderDate] = CONVERT(varchar(50), [Date], 101)
, [Total for that Date by Title] = SUM(SalesQuantity)
, [Average Qty in the FactSales Table] = @AverageQty
, [KPI on Avg Quantity] = CASE
WHEN Sum(SalesQuantity)
BETWEEN (@AverageQty- 5) AND (@AverageQty + 5) THEN 0
WHEN Sum(SalesQuantity) < (@AverageQty- 5) THEN -1
WHEN Sum(SalesQuantity) > (@AverageQty + 5) THEN 1
END
FROM DWPubsSales.dbo.FactSales AS FS
INNER JOIN DWPubsSales.dbo.DimTitles AS DT
ON FS.TitleKey = DT.TitleKey
INNER JOIN DWPubsSales.dbo.DimDates AS DD
ON FS.OrderDateKey = DD.DateKey
INNER JOIN DWPubsSales.dbo.DimPublishers AS DP
ON DT.PublisherKey = DP.PublisherKey
WHERE
@ShowAll = 'True'
OR
[Date] BETWEEN @StartDate AND @EndDate
