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.