Hi all,

Just a quick question about when to use views in contrast to stored
procedures.

For example, i have the following table:

Subject:
- SubjectID
- Name
- Published
- AccountID
- CreatedDt

Now my app requires to respresentations of the Data;
1) Administrators require: SubjectID, Name, Published, AccountID
2) Basic Users require: SubjectID, Name.

Now i know their are a number of ways i can do this:

1) Have two views, and let my app determine what info is returned.
2) Use ONE stored procedure which determines what type of user is requesting
the info and returns what is required.
3) Have two smaller stored procedures, and let my app determine which gets
executed; consequently what info is retrieved.

Would love some insight about these decisions, basically i am a little
concerned that i don't seem to use views a lot, so wonder if my approach
needs changing.

Cheers,
Adam

Rule of thumb: views are good for representing subsets of data or
denormalized (joined) data from multiple tables; stored procedures are good
for when you need to perform a lot of calculations or other manipulations on
the data, or when you need to return multiple result sets in one shot.

Here's a fourth alternative:

CREATE VIEW dbo.vSubject
AS
SELECT SubjectID, [Name],
CASE IS_MEMBER('db_owner') WHEN 1 THEN SubjectID ELSE '' END AS SubjectID,
CASE IS_MEMBER('db_owner') WHEN 1 THEN Published ELSE '' END AS Published,
CASE IS_MEMBER('db_owner') WHEN 1 THEN AccountID ELSE '' END AS AccountID
FROM Subject
GO

Use the IS_MEMBER or IS_SRVROLEMEMBER functions to selectively return data
in the view.  In the example above you can replace 'db_owner' with a
database role or Windows Group.  Use IS_SRVROLEMEMBER to check is the user
is a member of a server role.

Show quoteHide quote

Child X (develo***@dev.com) writes:
It's like asking when you should use tyres and when you should use
cars.

That is, views and stored procedures are not in oppisition to each
other. You can use none, one or both of them.

You use stored procedures when you want encapsulate some piece of
logic into the database. Be that simply the SQL syntax, or essential
parts of the business logic. Stored procedures is also what you use
when you want to restrict direct access to the tables. You revoke
access to the tables, but the user can get access to the data through
ownership chaining (or any of the new mechanisms added in SQL 2005).

Views permits you package the data from table in a different way for
some reason. It could be to encapsulate some complex condition,
for instance "Orders that have been shipped not paid". Since views
has its own set of permissions, views can also be used to give access
to table columns to which users do not have direct access.

It may be worth adding here a line of what is not. In SQL 2000 you can
define a view that goes like:

     SELECT TOP 100 PERCENT ....
     FROM   ....
     ORDER  BY ....

Any SELECT from the view will then appear to respect the ORDER BY. However
that is just the way it happens to be - and it don't happen to be that
way. If you want to encapsulate a certain order, stored procedures is
the only choice.

There are more options, for instance have the application to issue
different SELECT statements against the table depending on the user.

The decision here is not much about views or storeed procedures, but rather
where you want the business logic: in the database or in the middle/client
layer? I prefer to have the business logic in the database, so I would
go for your second alternative.

If you don't use views that often, don't worry. Neither do I. I think
views is mainly of interest when you have end users who run their own
queries on the database. Then views can serve to make things easier for
them. The same does not really apply when the queries are isssued from
the application, no matter whether you use stored procedures or not.

I did add two views to our database fairly recently. Their names coincide
with two tables that are no more. That is, I restructured how that
data was saved, but I did not feel like rewriting 180 stored procedures,
so I added compatibility views. There is another use for views.

Top 2 max operations
How to implement transactions
Get last date in result set
Managed Stored Procedures
Visual Basic 6.0 vs .NET Framework