Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 question about view and stored procedure

Author  Topic 

wentihenduo
Starting Member

8 Posts

Posted - 2013-03-02 : 04:14:27
Hi! I am confused about view and stored procedure.

see, view is just a virtual table that saved a select statement.

as I know, stored procedure can do the same select statement. And stored procedure can do more things view can not.

I mean, if view has no more ability, why it still exists? If I want to select some info from two different tables, is it better to do so via a view? Or stored procedure is exactly the same? If view is better, could anyone explain to me the reason? I heard that view can not save the result, but I also heard that if you add an index on a view, it can save the result so later we can read it faster. But I did not find any post prove the above opinion yet.

So, please help me here. Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-02 : 05:16:25
stored procedure is a precompiled batch of code which can be made parametrized if require , can do DML (insert/update/delete) etc. view on the other hand is a virtual table which may include data from more than one table. It cant perform any other operation other than just return a resultset which forms virtual table. Also it has to follow a lot of additional conditions if we need to update data in base tables through it. If we add an index on view, it will be physically stored and referred to as indexed view.
views are mostly used to merge data from tables from one or more dbs or to create an abstraction to expose only some of fields of table to set of users,

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-02 : 09:04:37
Another thing to consider is that you can join a view to other tables or views, apply where clauses, grouping etc. (i.e., all the things you can do to a table), but with a stored procedure, even when the stored procedure returns exactly similar result set as a view, it cannot be joined to other tables/views (without some acrobatic SQL maneuvers).
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-02 : 12:04:53
quote:
Originally posted by James K

Another thing to consider is that you can join a view to other tables or views, apply where clauses, grouping etc. (i.e., all the things you can do to a table), but with a stored procedure, even when the stored procedure returns exactly similar result set as a view, it cannot be joined to other tables/views (without some acrobatic SQL maneuvers).


it can if you use distributed query methods

see

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-03 : 11:31:52
A rose by any other name Visakh....
I called it "acrobatic SQL maneuver" in my previous post, you called it "distributed query method"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-03 : 22:36:32
quote:
Originally posted by James K

A rose by any other name Visakh....
I called it "acrobatic SQL maneuver" in my previous post, you called it "distributed query method"


sorry i'vent heard about terminology before..so thought you meant something different

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -