Difference between View and Stored Procedure in SQL Server

Difference between View and Stored Procedure

In this article I will explain you that what is difference between view and stored procedure in SQL Server?

View in SQL Server

Views are virtual tables that are compiled at run time. The data associated with views are not actually stored in the view, but it is stored in the base tables of the view. By using view we can join multiple tables and present the data as coming from a single table. Usually we put those columns in view that we need to retrieve again and again. Once you have created the view, you can query view like as table. We can make index on view.

For example consider we have two tables

      1)    Userdata table with columns userid, username
      2)    Salarydata table with columns salid, userid, salary

Create VIEW by joining above two tables

CREATE VIEW View_UserInfo
AS
BEGIN
SELECT Userdata.userid, Userdata.username, Salarydata.salary from userdata INNER JOIN Salarydata ON userdata.userid=salarydata.userid
END

By using above view we can get username or salary based on userid.

Stored Procedure

A stored procedure is a collection of SQL statements that has been created and stored in the database. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure

Benefits of using stored procedures

• Stored procedure allows modular programming. You can create the procedure once, store it in the database, and call it any number of times in your program. 
• Stored Procedure allows faster execution. If the operation requires a large amount of SQL code is performed repetitively, stored procedures can be faster. 
• Stored Procedure can reduce network traffic. 
• Stored procedures provide better security to your data

Example of creating Stored Procedure

USE mydatabase;
GO
CREATE PROCEDURE dbo.sp_retrivedata
AS
    SELECT FirstName, LastName FROM userinfo;
GO
EXEC sp_retrivedata;
EXEC dbo.sp_retrivedata;
GO

Posted By Prashant K Neelratan