Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Server- Difference between View and Stored Procedure

Aug 30, 2013
Introduction:

Here I will explain difference between SQL SERVER view and stored procedure in SQL Server 2008.  
Description:


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 ON a.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
For more information check this Stored procedure in SQL Server

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

Other Related Posts

© 2010-2012 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.