Database Reference
In-Depth Information
Adding Abstraction Layers
Microsoft and other vendors have often cautioned people against the direct use of table objects from their
database systems. Instead, the preferred model is to use abstraction layers whenever possible. Abstraction layers
for Microsoft SQL Server include the use of views, functions, and stored procedures. Let's take a look the two
most common ones, views and stored procedures.
Using Views
The most basic abstraction tool is a
view
. It is also the most limited. Views are saved
SELECT
statements that are
stored in Microsoft's SQL Server system tables. These hidden system tables store the text as well as binary valued
conversions, though the mechanism of how the SQL code is stored is immaterial to its use. When you create a
view, SQL Server stores it within the database, and you can use that
SELECT
statement again by just referring to it
in other SQL code.
Listing 13-22 shows an example of a view being wrapped around our current
SELECT
statement. Some
important things to note are that SQL views cannot contain a number of features we have included in our SQL
code so far. For example, you cannot include variables inside a view's definition, so the variable declarations
must be excluded. In addition, any use of those variables within the view will not work, and all those lines must
be excluded as well. Finally, you cannot use an
ORDER BY
clause within a view. As such, the
ORDER BY
clause must
also be removed.
Listing 13-22.
Creating a View
CREATE VIEW vQuantitiesByTitleAndDate
AS
--DECLARE
-- @ShowAll nVarchar(4) = 'True'
--, @StartDate nVarchar(10) = '09/13/1994'
--, @EndDate nVarchar(10) = '09/14/1994'
--, @Prefix nVarchar(3) = 'PS%'
--, @AverageQty int
--SELECT @AverageQty = Avg(SalesQuantity) FROM DWPubsSales.dbo.FactSales
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
