SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 question about view and stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wentihenduo
Starting Member

8 Posts

Posted - 03/02/2013 :  04:14:27  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 03/02/2013 :  05:16:25  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 03/02/2013 :  09:04:37  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 03/02/2013 :  12:04:53  Show Profile  Reply with Quote
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/

Edited by - visakh16 on 03/02/2013 12:05:45
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 03/03/2013 :  11:31:52  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 03/03/2013 :  22:36:32  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000