Everyone knows you can't parameterize a view. If you need parameters, you
should be using a stored procedure, right? Wait, is that right? Technically it is. Views simply do not accept parameters. The problem with
using a stored procedure is that it returns a resultset that you can't join to
directly. You can use the insert/exec syntax to put the results into a table and
work with it from there, or you could use OpenRowset.
That leaves you with the option of filtering against the view using a where
clause. Filtering outside the view, not inside. If you put the filter inside the
view then it applies to all users. I'm more interested in the scenario where the
rows returned from a view vary based on some other piece of information.
Let's say we have a reasonably simple view like this:
create view vCustomer as
select * from dbo.Customer where status = 1
Just for discussion, status of 1 indicates that the row is an active
customer. Let's also include a column called employee which contains the ID (int)
of the salesperson who would receive commission based on sales to that customer.
How would we find all the customers belonging to salesperson = 3?
select * from dbo.vCustomer where employeeid = @EmpID
Easy enough. We tell everyone to base all their reports on the vCustomer view
and to include the employee=@EmpID to get just the data they need. Soon we have
hundreds of reports all based on the view and life is good. Yes, I realize the
view is simple, but it could (and should) encapsulate other joins to make
reporting simple and to centralize code. Plus each report will probably include
main other tables that join to the view as needed for each report.
We have thousands of users and all are happy. Until one day, one manager says
"These reports are great! But what I really need is for each report to include
all the sales people who report to me!". Another manager hears this and says
"Yes! That would be so cool!". Off they go to their manager and so the story
continues until 37 minutes later when the directive arrives from far far up the
chain of command "Change all the reports so that they show employees that report
to that manager". Timeframe? Well, ASAP of course!
Humor aside, let's talk about that. Our new requirement is if the user is
just a salesperson, they see only their customers. If the user is a manager,
they see any customer they "own" plus all that are owned by their team. We
already have the relationships in a table called Employees that looks something
like this:
create table Employees (EmployeeID int, ManagerID int)
A self referencing table in best database fashion. What does that do to our
queries for the reports? We're going to need code that figures out if the user
is a plain salesperson or if they are a manager. If they are a manager, we'll
need a list of all employees that report to them. So, our query should return
one row based on the employeeid, plus potentially more rows if they have anyone
that reports to them. Something like this:
select employeeid from dbo.Employees where EmployeeID = @EmpID or ManagerID = @EmpID
That means we need to change every report to look something like this:
select * from dbo.vCustomer where employeeid in (select distinct employeeid from dbo.Employees
where EmployeeID = @EmpID or ManagerID = @EmpID)
So now we need to change a hundred reports or web pages or stored procedures.
Search and replace? It will work, just takes time to make sure you're not
breaking anything. Plus, you have to test each one to be sure. You do test don't
you?
Now we're back to where we started. It sure would be nice if we could just
modify that view, test it once, have all the reports leverage that change
automatically. Wouldn't it??? Is there a way to do it?
If we could pass in the @EmpID, it would be easy, it would be this:
create view vCustomers @EmpID int as
select * from dbo.vCustomer where employeeid in (select distinct employeeid from dbo.Employees
where EmployeeID = @EmpID or ManagerID = @EmpID)
Not valid syntax, just wishful thinking. Not only are we not able to pass a
parameter in, we can't even use a variable in the view. A view has to consist of
tables/views plus an optional where clause. If we had the parameter in a
table...that get you thinking?
Suppose, just suppose, that we have a standard entry point for all our
reports. One piece of code that we always use to login or open the connection or
run the report. If we could push a row into a table before we used the
view, we could join to it! To make it work, we would first define a table:
Create table LoginInfo (UserName varchar(255), employeeID int)
Then each time we connected, we would do this (doing the delete just to be
thorough):
delete from dbo.LoginInfo where userName=User_Name()
insert into dbo.LoginInfo (UserName, EmployeeID) values (User_Name(), @EmpID)
And we change our view to reference it:
create view vCustomers as
select * from dbo.vCustomer where employeeid in (select distinct employeeid from dbo.Employees
where
EmployeeID in (Select employeeid from dbo.LoginInfo where username=User_Name())
or
ManagerID in (Select employeeid from dbo.LoginInfo where username=User_Name())
Effectively we've parameterized the view. Or hacked a solution depending on your
point of view. It's not a miracle cure. It relies on the existence of a
centralized login/connection process where we can do the insert. If we had to
add that insert to every report we'd probably be better off doing search/replace
or refactoring the view. This also won't work if all the users have the same
user name, an alternative would be to use @@SPID.
The situation to illustrate
the problem/solution is contrived. More thought up front in the view or the base
query against the view probably would have headed this off. My goal here is to
get you thinking about changing the behavior of a view without changing it's
signature/interface, and to get you thinking that a view should really be about
encapsulation and code reduction.
