Database Reference
In-Depth Information
WHERE
@ShowAll = 'True'
OR
[OrderDate] BETWEEN @StartDate AND @EndDate
AND
[TitleId] LIKE @Prefix
ORDER BY PublisherName, [Title], [OrderDate]
Now we get the same results as before, but client reporting applications that expect to only use views will
have them available as needed.
One change that should be noted is that since the view contains an alias column, our SQL code must be
modified to include the new column names. You can see an example of this in the modified version of the
WHERE
clause shown in Listing 13-24. Whereas before you were using the
[Date]
column, you must now use the
[OrderDate]
column as defined by the view. The same is true in places where we reuse the
Sum(SalesQuantity)
in our calculations inside of the
CASE
operator; we must now use the
[Total for that Date by Title]
column
of the view.
When this
SELECT
statement is run, all the results from the view will still be gathered in memory, but the final
results will include only filtered data. Figure
13-20
shows what this looks like.
Figure 13-20.
Fitered results from the view with the KPIs added and results ordered
Note that the result set looks remarkably similar to the one before you started using the view
(see Figure
13-18
). This is because we are getting the same results. The only difference is that now we are
using a view.
Using Stored Procedures
Perhaps the most flexible tool you can use for abstracting your report data is SQL stored procedures. Stored
procedures can include almost all
SELECT
clauses, built-in functions, operators, and even multiple
SELECT
statements. Because of this flexibility, chances are very high that you will want to create stored procedures for
your reporting applications.
