Views & Stored Procedure in SQL Server 2005

• A view consists of a SELECT statement that stored with a database. Because views are stored as part of the database, they can be managed independently of the applications that use them.
  
• A view behaves like a virtual table. Since you can code a view name anywhere you can code a table name. a view is sometimes called a viewed table.
  
• Views can be used to restrict the data that a user is allowed to access or to present data in a form that is easy for the user to understand. In some database users may be allowed to access data only through views.

In this example I am using my Vendor database which has these fields.

Here is my database:

 
Figure 1.

Create View :

USE [Vendor]
GO
/****** Object: View [dbo].[VendorData] Script Date: 08/07/2008 23:27:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[VendorData] AS
SELECT VendorId,VendorFName,VendorLName,VendorCity,VendorState,VendorCountry,PostedDate,VendorDescription
FROM Vendor

You can execute your view like this, in this example VendorData is my View name.

Use Vendor
GO
SELECT * FROM VendorData WHERE VendorState = 'PA' ORDER BY PostedDate
GO

Output:


Figure 2.

This is it.

Stored Procedure

A stored procedure is a set of one or more SQL statements that are stored together in database. To create a stored procedure use CREATE PROCEDURE statement. To use the stored procedure you send a request for it to be executed. When server recieves the request, it executes the stored procedure.

Stored procedures assist in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.

Stored procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server. The results do not have to be returned to the client to have the conditional logic applied; all of the work is done on the server.

There are some concepts of stored procedures.

• A stored procedure is one or more SQL statements that have been compiled and stored with database. A stored procedure can be started by application code on the client.
  
  
• Stored procedure can improve database performance because the SQL statements in each procedure are only compiled and optimized the first time they are executed. In sontrast SQL statements that are sent from a client to the server have to be compiled and optimized everytime ther are executed.
  
  
• In addition to SELECT statement, a stored procedure can contain othe SQL statements such as INSERT,UPDATE,DELETE. It also contain control-of-flow language.
  
  
• A trigger is a special type of procedure that executes when rows are inserted, updated or deleted from table.
  
  
• A user defined function(UDF) is a special type of procedure that can return a value or a table.