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.
In this example, I am using my Vendor database which has these fields.
Here is my database:
1.gif
Figure 1.

Create View in SQL Server

  1. USE [Vendor]
  2. GO
  3. /****** Object: View [dbo].[VendorData] Script Date: 08/07/2008 23:27:34 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. ALTER VIEW [dbo].[VendorData] AS
  9. SELECT VendorId,VendorFName,VendorLName,VendorCity,VendorState,VendorCountry,PostedDate,
  10. VendorDescription
  11. FROM Vendor
You can execute your view like this, in this example, VendorData is my View name.
  1. Use Vendor
  2. GO
  3. SELECT * FROM VendorData WHERE VendorState = 'PA' ORDER BY PostedDate
  4. GO
Output
Create View in SQL Server
Figure 2.

Stored Procedure in SQL Server

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 receives 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.
Example
  1. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5. -- =============================================
  6. -- Author: <Author,,Name>
  7. -- Create date: <Create Date,,>
  8. -- Description: <Description,,>
  9. -- =============================================
  10. CREATE PROCEDURE spVendorByState
  11. @VendorState varchar(50)
  12. AS
  13. BEGIN
  14. -- SET NOCOUNT ON added to prevent extra result sets from
  15. -- interfering with SELECT statements.
  16. SET NOCOUNT ON;
  18. -- Insert statements for procedure here
  19. SELECT VendorId,VendorFName,VendorLName,VendorCity,VendorState,VendorCountry,PostedDate,
  20. VendorDescription
  21. FROM Vendor Where VendorState = @VendorState ORDER BY PostedDate
  23. END
  24. GO
You can execute your stored procedure like this:
execute stored procedure
Figure 3.
Output looks like this
pic4.jpg
Figure 4.
This is it.