What is the difference between a stored procedure

Question

I am confused about a few points:

1. What is the difference between a stored procedure and a view?

2. When should I use stored procedures, and when should I use views, in SQL Server?

3. Do views allow the creation of dynamic queries where we can pass parameters?

4. Which one is the fastest, and on what basis is one faster than the other?

5. Do views or stored procedures allocate memory permanently?

6. What does it mean if someone says that views create a virtual table, while procedures create a materials table?

Please let me know about more points, if there are any.

Answer 1

A view represents a virtual table. You can join multiple tables in a view and use the view to present the data as if the data were coming from a single table.

A stored procedure uses parameters to do a function... whether it is updating and inserting data, or returning single values or data sets.

Creating Views and Stored Procedures - has some information from Microsoft as to when and why to use each.

Say I have two tables:

tbl_user Columns: .user_id, .user_name, .user_pw

tbl_profile Columns: .profile_id, .user_id .profile_description

So if i find myself querying from those tables ALOT... instead of doing the join in EVERY peice of sql i would define a view like:

CREATE View vw_user_profile
AS
  Select A.user_id, B.profile_description
  FROM tbl_user A left join tbl_profile B on A.user_id = b.user_id
GO

So in the future if i want to query profile_description by user id... all i have to do is

SELECT profile_description FROM vw_user_profile WHERE user_id = @ID

THAT code could be used in a stored procedure like:

create procedure dbo.getDesc
 @ID int
AS
begin
SELECT profile_description FROM vw_user_profile WHERE user_id = @ID
END
GO

So later on i can call

dbo.getDesc 25

and I will get the description for user id 25. where the 25 is your parameter.

There is obviously A LOT more detail, but, this is just the basic idea.

Answer 2

In addition to the above comments, I would like to add few points about Views.

1. Views can be used to hide complexity. Imagine a scenario where 5 people are working on a project but only one of them is too good with database stuff like complex joins. In such scenario, he can create Views which can be easily queried by other team members as they are querying any single table.
2. Security can be easily implemented by Views. Suppose we a Table Employee which contains sensitive columns like Salary, SSN number. These columns are not supposed to be visible to the users who are not authorized to view them. In such case, we can create a View selecting the columns in a table which doesn't require any authorization like Name, Age etc, without exposing sensitive columns (like Salary etc. we mentioned before). Now we can remove permission to directly query the table Employee and just keep the read permission on the View. In this way, we can implement security using Views.

Answer 3

Main difference is that when you are querying a view then it's definition is pasted into your query. Procedure could also give results of query, but it is compiled and for so faster. Another option are indexed views..

Answer 4

A SQL View is a virtual table, which is based on SQL SELECT query. A view references one or more existing database tables or other views. It is the snap shot of the database whereas a stored procedure is a group of Transact-SQL statements compiled into a single execution plan.

View is simple showcasing data stored in the database tables whereas a stored procedure is a group of statements that can be executed.

A view is faster as it displays data from the tables referenced whereas a store procedure executes sql statements.

Check this article : View vs Stored Procedures . Exactly what you are looking for