Differencebetween a Stored Procedure and a View

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

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.

Performance difference between view and stored procedure

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.

What is the difference between Stored Functions and Views in DB?

View:
A view is a virtual table. It does not physically exist. Rather, it is created by a query joining one or more tables. View returns a table.

Stored procedure: A stored procedure is a group of Transact-SQL statements compiled into a single execution plan.

stored procedures returns Output parameters,return codes (which are always an integer value),
a result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure,a global cursor that can be referenced outside the stored procedure.

key benefits of stored procedure are Precompiled execution, reduced client/server traffic,efficient reuse of code, programming abstraction and enhanced security controls.

Update:

A stored function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.
1) Return Type: The header section defines the return type of the function. The return datatype can be any of the oracle datatype like varchar, number etc.
2) The execution and exception section both should return a value which is of the datatype defined in the header section

Stored Procedures Vs. Views

Well, I'd use stored proc for encapsulation of code and control permissions better.

A view is not really encapsulation: it's a macro that expands. If you start joining views pretty soon you'll have some horrendous queries. Yes they can be JOINed but they shouldn't..

Saying that, views are a tool that have their place (indexed views for example) like stored procs.

SQL-Server Performance: What is faster, a stored procedure or a view?

Stored Procedures (SPs) and SQL Views are different "beasts" as stated several times in this post.

If we exclude some [typically minor, except for fringe cases] performance considerations associated with the caching of the query plan, the time associated with binding to a Stored Procedure and such, the two approaches are on the whole equivalent, performance-wise. However...

A view is limited to whatever can be expressed in a single SELECT statement (well, possibly with CTEs and a few other tricks), but in general, a view is tied to declarative forms of queries. A stored procedure on the other can use various procedural type constructs (as well as declarative ones), and as a result, using SPs, one can hand-craft a way of solving a given query which may be more efficient than what SQL-Server's query optimizer may have done (on the basis of a single declarative query). In these cases, an SPs may be much faster (but beware... the optimizer is quite smart, and it doesn't take much to make an SP much slower than the equivalent view.)

Aside from these performance considerations, the SPs are more versatile and allow a broader range of inquiries and actions than the views.

Which one is best View or Stored procedure in sql server for data access purpose

Views and stored procedures serve entirely different purposes. Views are a convinient way to refer to a complex relational set (such as one that joins across many tables) as a flat table without actually forcing the data to be manifested. You use a view to clean up SQL code. Your stored procedures could call views. Views are often used for permission control. You can grant a database user access to a view without granting them access to the underlying tables. This grants the user column level permissions on the columns in the view which is a far more granular method for permission control than granting access to whole tables.

Stored procedures are used to keep often used functionality together as a unit. To be honest, SPs are falling out of favor among many programmers. While you are correct that SPs have their execution plans cached, dynamic SQL has had execution plan caching since SQL Server 2000 (I believe that's the correct version). The only speed gain you're going to get by going with SPs is by sending less data over the network, and that's going to be extremely minimal. SPs tend to make code more brittle and require changes to the DB to occur when application changes don't really warrant it. For example, if you just wanted to change the conditions for which rows you're selecting. Using SPs, you're going to have to roll changes out to the application and the database code. If you're using dynamic SQL or an ORM tool, you only need to make changes to the application which simplifies deployment. There is absolutely a time and place for SPs, but they don't need to be your only method for interacting with the database.

Also, if you're worried about performance, you can materialize views which reduces the need to repeatedly query the underlying tables. This could greatly enhance your performance if you feel the need to add the extra overhead on inserts/updates that materializing views induces.