Views & Stored Procedure in SQL Server 2005

In this article I am going to describe how to use views in SQL Server 2005 database. A view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is stored in the database. It is a query stored as an object. Hence, a view is an object that derives its data from one or more tables. These tables are referred to as base or underlying tables. Once you have defined a view, you can reference it like any other table in a database.

A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them. Users cannot see or access the remaining data in the underlying tables. A view also serves as a mechanism to simplify query execution. Complex queries can be stored in the form as a view, and data from the view can be extracted using simple queries.

• 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.

Example:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE spVendorByState
          @VendorState varchar(50)
AS
BEGIN
          -- SET NOCOUNT ON added to prevent extra result sets from
          -- interfering with SELECT statements.
          SET NOCOUNT ON;

          -- Insert statements for procedure here
          SELECT VendorId,VendorFName,VendorLName,VendorCity,VendorState,VendorCountry,PostedDate,
          VendorDescription
          FROM Vendor Where VendorState = @VendorState ORDER BY PostedDate

END
GO

You can execute your stored procedure like this:


Figure 3.

Output looks like this:


Figure 4.

This is it.