When to use view and stored procedures ?

When to use view and stored procedures?
Whats the deep difference between views and stored procedures?
Please any one can explain me about views and store procedures.

Thanks In Advance.

more ▼

asked Feb 03 '10 at 06:29 AM in Default

Aftab gravatar image

21 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

They are very different concepts, to be honest. A view is a single result set that presents information from one or more sources in a single query statement. It has a fixed schema (i.e. the result set it returns will always have the same columns) and it has no parameters.

Stored procedures are simply stored queries. They can have parameters, you can choose which parts execute or not with control logic, they can create and destroy objects in the database, they can return any number of result sets.

In terms of when to use them - That's a topic that can't be answered simply, but the basic way I would put it is that a view is for looking at a set of data in the same way easily, and stored procedures are for repeating a set of actions in the same way easily.

more ▼

answered Feb 03 '10 at 06:38 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left

As Matt correctly said, they are very different concepts. An example would be like, if an application has a login page, a view would contain the login information and a stored procedure would be used to verify the login information. A view is a container whereas a stored procedure is a method or a function which can be executed to perform a task.

more ▼

answered Feb 03 '10 at 09:42 AM

Abhinav Chadha gravatar image

Abhinav Chadha

(comments are locked)
10|1200 characters needed characters left

Views : They are the virtual table which consists of one or more rows and columns from different real tables of the Database. It is the template of rows and columns of multiple tables. You cannot pass any parameters here.

Stored Procedures : They are a collection of pre-executed sql Statements where you can send the parameters as input and retrieve the output data.

Views can be used in the Stored Procedure but the Stored Procedure cannot be used in Views...!
more ▼

answered Sep 05 '12 at 11:14 AM

jsylvester89 gravatar image


(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 03 '10 at 06:29 AM

Seen: 9681 times

Last Updated: Sep 05 '12 at 11:14 AM