What is the difference between view and stored procedure in sql server?

View in SQL Server

A view represents a virtual table. By using view we can join multiple tables and present the data as coming from a single table.

For example consider we have two tables

      1)    UserInformation table with columns userid, username

      2)    SalaryInformation table with columns salid, userid, salary

Create VIEW by joining above two tables

CREATE VIEW VW_UserInfo

AS

BEGIN

SELECT a.userid,a.username,b.salary from UserInformation a INNER JOIN SalaryInformation b ONa.userid=b.userid

END

By using above view we can get username or salary based on userid for that we need to create procedure like as shown below

CREATE PROCEDURE GetUserInfo

@uid INT

AS

BEGIN

SELECT username from VW_UserInfo WHERE userid=@uid

END

If you observe above procedure it’s like getting username from single table (VW_UserInfo) by passing userid

Stored Procedure

A stored procedure is a group 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

Sample of creating Stored Procedure

USE AdventureWorks2008R2;

GO

CREATE PROCEDURE dbo.sp_who

AS

    SELECT FirstName, LastName FROM Person.Person;

GO

EXEC sp_who;

EXEC dbo.sp_who;

GO

DROP PROCEDURE dbo.sp_who;

GO

http://pramadha.com/

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s