Passing arguments to views in SQL Server

Question

Suppose I have a view that rolls up data via a GROUP BY clause:

CREATE VIEW InvoiceAmountsByCustomer AS
  SELECT
    Customer.CustomerID,
    SUM(Invoice.TotalAmount) AS TotalInvoiceAmount
  FROM
    Customer LEFT OUTER JOIN Invoice ON Customer.CustomerID = Invoice.CustomerID
  GROUP BY Customer.CustomerID

Now, suppose I want the view to restrict which invoices are included in the total based on a date range. To illustrate (pretending {From Date} and {To Date} contain the date range):

CREATE VIEW InvoiceAmountsByCustomer AS
  SELECT
    Customer.CustomerID,
    SUM(Invoice.TotalAmount) AS TotalInvoiceAmount
  FROM
    Customer LEFT OUTER JOIN Invoice ON Customer.CustomerID = Invoice.CustomerID
  WHERE
    Invoice.Date BETWEEN {From Date} AND {To Date}
  GROUP BY Customer.CustomerID

What do I replace {From Date} and {To Date} with so that I can pass their values (directly or indirectly) to the view? For example, is it possible to associate these values with the current session?

CREATE VIEW InvoiceAmountsByCustomer AS
  SELECT
    Customer.CustomerID,
    SUM(Invoice.TotalAmount) AS TotalInvoiceAmount
  FROM
    Customer LEFT OUTER JOIN Invoice ON Customer.CustomerID = Invoice.CustomerID
  WHERE
    Invoice.Date BETWEEN GetUserDefinedSessionValue('From Date') AND GetUserDefinedSessionValue('To Date')
  GROUP BY Customer.CustomerID

where GetUserDefinedSessionValue is an imaginary function that returns values associated with the current session. Another imaginary function, SetUserDefinedSessionValue would be called by the client prior to querying the view:

SetUserDefinedSessionValue('From Date', ...)
SetUserDefinedSessionValue('To Date', ...)
SELECT * FROM InvoiceAmountsByCustomer

My imaginary example is only meant to illustrate one way I envision these values being passed to the view.

Note: This is a trivial example and the real situation is much more complex, preventing me from executing a GROUP BY query directly by the client.

Answer 1

It is possible to call User Defined Functions within a view, yes, but I would rather use a User Defined Table Function, where you pass in the parameters you require, and this in turn returns the data you wish for.

User Defined Functions

Answer 2

Here is one solution:

CREATE TABLE dbo.ViewDates (theDate DATETIME)

To query the database:

TRUNCATE TABLE viewDates

INSERT INTO viewDates VALUES (@fromDate)
INSERT INTO viewDates VALUES (@toDate)

To tweak the view

JOIN (select min(thedate) as StDate,max(theDate) as EnDate ) dt 
  ON invoice.date BETWEEN dt.StDate and dt.EndDate

Note that this will only work for one user at a time, if you need multiple users, you will need to add a user-id to the table, populate it, and adjust the query a bit...