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, with columns: user_id, user_name, user_pwtbl_profile, with columns: profile_id, user_id, profile_descriptionSo, if I find myself querying from those tables A LOT... instead of doing the join in EVERY piece 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
Thus, if I want to query profile_description by user_id in the future, 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, this is just the basic idea.