Database Reference
In-Depth Information
JOIN DWPubsSales.dbo.DimDates
On pubs.dbo.sales.ord_date=DWPubsSales.dbo.DimDates.date
JOIN DWPubsSales.dbo.DimTitles
On pubs.dbo.sales.Title_id=DWPubsSales.dbo.DimTitles.TitleId
JOIN DWPubsSales.dbo.DimStores
On pubs.dbo.sales.Stor_id=DWPubsSales.dbo.DimStores.StoreId
Go
Once you create a stored procedure, you run its code by executing it. The code to execute a stored procedure
is in Listing 6-25.
Listing 6-25.
Executing a Stored Procedure
Execute pEtlFactSalesData
If a stored procedure contains a SQL select statement, the selected results are returned to the client software.
Client software includes SQL Server Management Studio, but as we show in Chapter 8, SSIS also acts as client
software when it processes the stored procedure results.
As mentioned previously, one advantage of using stored procedures is their ability to process parameters.
Parameters can be used to filter results and modify output or supplied transactional data. For example, you might filter a
select statement specifically to return results that were associated with the current day's sales, as shown in Listing 6-26.
Listing 6-26.
Altering the Stored Procedure to Use a Parameter
Alter
Procedure pEtlFactSalesData
( @OrderDate datetime )
as
Select
[OrderNumber]=ord_num
, [OrderDateKey]=DateKey
, [TitleKey]=DimTitles.TitleKey
, [StoreKey]=DimStores.StoreKey
, [SalesQuantity]=qty
From pubs.dbo.sales
JOIN DWPubsSales.dbo.DimDates
On pubs.dbo.sales.ord_date=DWPubsSales.dbo.DimDates.date
JOIN DWPubsSales.dbo.DimTitles
On pubs.dbo.sales.Title_id=DWPubsSales.dbo.DimTitles.TitleId
JOIN DWPubsSales.dbo.DimStores
On pubs.dbo.sales.Stor_id=DWPubsSales.dbo.DimStores.StoreId
Where pubs.dbo.sales.ord_date=@OrderDate
Once a stored procedure is created, you can execute it by supplying the current date, using a built-in
function such as the SQL Server
GETDATE()
function. If you were interested in incrementally loading your fact
tables with only data that came in on a particular day, a stored procedure similar to this could be useful. Listing
6-27 shows an example of executing a stored procedure with a parameter.
Listing 6-27.
Executing a Stored Procedure with a Parameter
Declare @TodaysDate datetime
Set @TodaysDate=Cast( GetDate() as datetime )
Execute
pEtlFactSalesData
@OrderDate=@TodaysDate
