Difference between View and Stored Procedure

What is the difference between View and Stored Procedure? Can views accept input parameters as stored procedure? If yes,how could I do that? I am new to database and working on MS SQL,if someone could suggest me good reference websites then that would be great.

Questions by preetiv

Editorial / Best Answer

atdhdrolla  

  • Member Since Mar-2007 | Jul 16th, 2008


View - A View in simple terms is a subset of a table. It can be used to retrieve data from the tables, Insert, Update or Delete from the tables. The Results of using View are not permanently  stored in the database.

Stored Procedure -  A stored procedure is a group of SQL statements which can be stored into the database and can be shared over the netwrok with different users.

Showing Answers 1 - 3 of 3 Answers

View is nothing but an imaginary table .it contains data at run time only not created in the database where as stored procedure can be used to store business logic..it is created at database level.
 main difference is stored procedure exits in database where as view does not exits in database.

ashwin890

  • Jul 11th, 2008
 

View is virtual table With view you can restirct the user to see what is required for him and stored procedure is pre compiled transact sql statments which is compiled once stored on the server

  Was this answer useful?  Yes

View - A View in simple terms is a subset of a table. It can be used to retrieve data from the tables, Insert, Update or Delete from the tables. The Results of using View are not permanently  stored in the database.

Stored Procedure -  A stored procedure is a group of SQL statements which can be stored into the database and can be shared over the netwrok with different users.

Okay, to put in simple terms a VIEW is a "small" version of a DB2 table. In other words it does not have all the columns in that table. This is a good method of hiding information from unauthorized parties. So, in simple terms it is nothing but a smaller snapshot of the columns of a specific table. 

If the EMPLOYEE table contains these Columns: 
- LastName
- FirstName
- Dept
- Salary
A view of this table could be defined with just the LastName, FirstName and Dept. 

Okay, now a STORED PROCEDURE is totally different. It has absolutely no relation to a view. So, in simple terms again, a STORED PROCEDURE is a "program" that resides on the database server and interrogates the DB2 data via SQL statements. This database object can be called by many applications whether local or remote. The stored procedure uses parameters passed from the calling program and sends back output data as parameters and also cursor result sets. These result sets could be obtained and manipulated  using a pointer in the calling program. Oh, just to confuse you further, a stored procedure could use table VIEWS. 
Wow! This was a mouthful. Hope this helps.

View
View is a virtual table.
View is derive from base table.if we delete base table view also automatically will delete.
There are 2 types of view
       1) Simple  view.
       2) Complex view.

Procedure
Pre-compiled execution.
There are 3 types of  procedure.
       1) in parameter
       2) out  parameter
       3) in/out parameter
                   

VIVEK KUMAR CYHOUBEY

  • Aug 18th, 2011
 

Difference between views and stored procedure

1. View is a virtual table you cant any parameter in view

2. Stored procedure is collection of sql statement that are previously created and saved on the database server for reduce network traffic.

  Was this answer useful?  Yes

Pradeep

  • Feb 10th, 2012
 

The view is collection tables and it can performs the DML operations like insert, delete, update, it can view the data but it cant stored permanently stored in db. By using view you can acquire the data. By use in view you can execute only on statement at a time stored procedure is pl/sql block it stored permanently in db, by using stored procedure you can execute more then one statement at a time. You can re-use the code and data.

  Was this answer useful?  Yes

KSPradeep

  • Feb 10th, 2012
 

VIEW: View doesnt accepts parameters, by using view we can building block in large query, it can contain only one select query, it cant preform modifications to any table, it can be used as the target for dml queries.

STORED PROCEDURE: Stored procedure can accepts parameters (in, out, in out), it cant be used as a building block in large query. It can contains several select statement (if, else, loop). It can perform modifications to one or more than one tables, it cant be used as the target for DML queries

  Was this answer useful?  Yes

nirzneerz

  • Mar 29th, 2012
 

A View is basically a table that only exists when you use the view in a query. Its considered virtual table because it acts like a table, and the same operations that can be performed on a table can be performed on a view. Virtual table doesn't stay in the database, it gets created when we use the view and then delete it.

  Was this answer useful?  Yes

biswajit talukder

  • Apr 28th, 2012
 

View is a virtual table. physically is not exit. but procedure is physically exit,which has mainly 2 types
1)local procedure and
2)stored procedure,
store procedure maid by the user. and local procedure made by the SQL.

  Was this answer useful?  Yes

naveenreddy bolla

  • Feb 21st, 2013
 

View: View is virtual table. It is using for security purpose means whenever we need to give only some permissions to the different user then create the view from table with required columns. Their is no physical presence of view data anywhere in database.

Stored Procedure: It is stored permanently in database. It takes input parameters and gives the output. It is stored once and reused many times.

  Was this answer useful?  Yes

Test

  • Oct 16th, 2013
 

View does not have parameters , SP can have input / output parameters . View is another way to view table data . SP is pre-compiled object which can change /add data into tables.

  Was this answer useful?  Yes

Leena Roja

  • Jul 9th, 2015
 

Views are virtual tables or subset of permanent tables for easier access of data which is stored in multiple tables. Views contains rows, columns just like a real table. The fields in a view are nothing but the fields retrieved from single or multiple tables.

We can perform insert, update, delete operations on views, similarly those operation results will affect on permanent table. but if we have written view for multiple tables, data insertion is not possible , it will throw error as "insertion cannot done on multiple tables", but we can perform insertion on view only if the view is created on single table. Assume that, if employee table (permanent table) has 4 fields id,name,designation,location.

Now we are creating view on employee table by name employee_view with fields name,designation and performing insertion. it will accept the insertion operation on view because it is created on single table. if we are inserting values for name,designation by view(employee_view) it will take values and the same will be affected into permanent table also. but the remaining fields will be taken NULL values in employee table(permanent table).

Similarly we can perform update,delete operations also.
when coming to insert,update,delete operations on views created on multiple tables. For sure ,insertion wont possible. whereas update and delete, we cant confirm but we can perform any type of insertions, updations and deletions using and on stored procedures.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions