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:
tbl_profile, with columns:
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
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:
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.