Views vs. Stored Procedures 1

[Terpsfan]

I'm trying to find if there is a clear delineation as to whether to use a view or a stored procedure. I know in cases where you need to use an action query or you have logic you should use a stored procedure. But how about in cases where you are using just a select statement with no logic? Should that always be a View, or could you use a Stored Procedure for it? I've used simple select queries as Stored Procedures and have wondered if that is a no no.

 

[sguslan]

Well, the befiti of a stored procedure vs views is speed. A view when used to fetch data from SQL is very talkative and generate more network load. SPs will be executed on the sql server and te result is flushed to the client at once.
SP are more secure in that the user has no way to view data from tables he/she has to explicit select priveleges. Where as in the case of a view all data available through the view can be viewed directly through a client like sql analyzer or Access..... ________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[JamesLean]

sguslan,
As long as the view and the underlying tables have the same owner, users do NOT need permissions on the tables to use the view and as such they cannot query the tables directly. One of the main uses for views is as a security function, eg to restrict access to certain columns/rows in a table.

Omega36,
Whether to use SPs or views is really dependent on what you are trying to achieve. They both have specific uses and pros/cons. There isn't really a case for saying you should always use one or the other.

Perhaps you could let us know if there's a specific problem you have at the moment and are wondering how best to solve it? --James

 

[SQLSister]

Views are often used when you have complex joins that will be used in multiple stored procedures. This helps keep programming errors from happening due to the complex nature of the joins.

They are also used when you want to make sure that access is limited to certain records and columns. For instance, you have three regions. You can build views for each region that only allows access to the data for the region. Then build your stored procedures for each region based on the view instead of the base tables.