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_pw
tbl_profile
, with columns:profile_id
,user_id
,profile_description
So, 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.