Dynamic Views

for RuBoard

When you access a view, a query plan is constructed by combining the original SELECT statement that was used to create the view with the one you're using to query it. Normally, the selection criteria you specified when you built the view are combined with any specified by your query, and the composite is passed on to the server engine for further processing.

Most views that include selection criteria impose static (or deterministic) criteriathe selection logic that's combined with the SELECT statement accessing the view never changes, regardless of how many times the view is queried. The dynamic portion of the composite query usually comes from the user -supplied SELECT, not the view. With the exception of views that use joins to link other views and tables, the criteria the view supplies to filter the result set remains the same from use to use. Most of the time this is adequate, but there are times when it's handy to be able to set up a dynamic (or nondeterministic) viewa view with selection criteria that vary based on factors external to it.

A dynamic view is simply one with selection criteria that can change based on the evaluation of the expressions in its WHERE or HAVING clauses. This is an easy concept that can come in quite handy. Rather than evaluating to constants, these expressions are nondeterministic: they return different values from execution to execution, based on environmental or session conditions. The best example of such a view is one that returns a result set based on a nontabular expression. Listing 9-14 presents one that lists the sales for the current date using the nontabular GETDATE() function:

Listing 9-14 A dynamic query allows you to use nondeterminism to your advantage.
 CREATE VIEW DAILY_SALES AS SELECT * FROM sales WHERE ord_date BETWEEN CONVERT(char(8),GETDATE(),112) AND CONVERT(char(8),GETDATE(),112)+' 23:59:59.999' 

You can add some rows to sales to see how this works:

 INSERT sales VALUES ('8042','QA879.1',GETDATE(),30,'Net 30','BU1032') INSERT sales VALUES ('6380','D4482',GETDATE(),11,'Net 60','PS2091') INSERT sales VALUES ('6380','D4492',GETDATE()+1,53,'Net 30','PS2091') SELECT * FROM DAILY_SALES stor_id ord_num              ord_date                    qty    payterms     title_id ------- -------------------- --------------------------- ------ ------------ -------- 6380    D4482                1999-06-24 19:14:33.657     30     Net 60       PS2091 8042    QA879.1              1999-06-24 19:13:26.230     30     Net 30       BU1032 

This view uses GETDATE() to limit the sales returned to those whose ord_date is today. The criteria actually processed by the server will vary based on the current date. Today, its WHERE clause will be expanded to today's date and the first two rows that were inserted will show up. Tomorrow, it will evaluate to tomorrow's date and the third row will show up. This is the nature of dynamic views: The criteria that are actually processed by the server change from use to use based on external factors.

Here's another example that uses CASE to make the view even more dynamic. This code improves on the previous example by making it aware of weekends. Because no sales occur on weekends, this code returns the sales for either the previous Friday or the upcoming Monday when the current date falls on a weekend (Listing 9-15):

Listing 9-15 A dynamic view that uses a nondeterministic function and CASE.
 CREATE VIEW DAILY_SALES AS SELECT * FROM sales WHERE ord_date BETWEEN                (CASE DATEPART(DW,CONVERT(char(8),GETDATE(),112))               WHEN 1 THEN CONVERT(char(8),GETDATE()+1,112)               WHEN 7 THEN CONVERT(char(8),GETDATE()-1,112)               ELSE CONVERT(char(8),GETDATE(),112)               END) AND            (CASE DATEPART(DW,CONVERT(char(8),GETDATE(),112))               WHEN 1 THEN CONVERT(char(8),GETDATE()+1,112)               WHEN 7 THEN CONVERT(char(8),GETDATE()-1,112)               ELSE CONVERT(char(8),GETDATE(),112)               END+' 23:59:59.999') 

You can use other nontabular functions to create similar sliding or dynamic views. For example, SUSER_SNAME() could be used to limit the rows returned according to user name. HOST_NAME() could be used to filter based on machine name . Whatever the case, the SELECT statement used to query the view doesn't change (in the previous examples, it's always a simple SELECT *); only the criteria that the view provides to filter the result set do.

for RuBoard


The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
ISBN: 201700468
EAN: N/A
Year: 2005
Pages: 223

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net