Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

People also ask

What is view and stored procedure in SQL?

Views: Database views allow you to create "virtual tables" that are generated on the fly when they are accessed. A view is stored on the database server as an SQL statement that pulls data from one or more tables and (optionally) performs transformations on that data.

Can a stored procedure be used in a view?

You cannot call a stored proc from inside a view. It is not supported. However, you can make views call other views or table-valued user-defined functions. For the latter, you must make sure that you're using inline functions.

What is the difference between views stored procedures and triggers?

Stored procedures can be invoked explicitly by the user. It's like a java program , it can take some input as a parameter then can do some processing and can return values. On the other hand, trigger is a stored procedure that runs automatically when various events happen (eg update, insert, delete).

What is difference between stored procedure and table?

Tables are the basic data storage objects in a database. Views are virtual tables, pre-canned SELECTs. Stored procedures are programming objects returning a SELECT like result set and optionally output parameters.


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.


Plenty of info available here

Here is a good summary:

A Stored Procedure:

  • Accepts parameters
  • Can NOT be used as building block in a larger query
  • Can contain several statements, loops, IF ELSE, etc.
  • Can perform modifications to one or several tables
  • Can NOT be used as the target of an INSERT, UPDATE or DELETE statement.

A View:

  • Does NOT accept parameters
  • Can be used as building block in a larger query
  • Can contain only one single SELECT query
  • Can NOT perform modifications to any table
  • But can (sometimes) be used as the target of an INSERT, UPDATE or DELETE statement.

First you need to understand, that both are different things. Stored Procedures are best used for INSERT-UPDATE-DELETE statements. Whereas Views are used for SELECT statements. You should use both of them.

In views you cannot alter the data. Some databases have updatable Views where you can use INSERT-UPDATE-DELETE on Views.


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


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.

  1. A VIEW is a dynamic query where you can use a "WHERE"-Clause
  2. A stored procedure is a fixed data selection, which returns a predefined result
  3. Nor a view, nor a stored procedure allocate memory. Only a materialized view
  4. A TABLE is just one ENTITY, a view can collect data from different ENTITIES or TABLES