SQL View and Stored Procedure

A practical guide with SQL

VIEW

View Anatomy

When you use the SELECT statement to query data from one or more tables, you get a result set.

For example, the following statement returns the EmployeeId, Full name, job, salary and ordered_job_salary:

Next time, if you want to get the same result set, you can save this query into a text file, open it, and execute it again.

SQL Server provides a better way to save this query in the database catalog through a view.

A View is a named query stored in the database catalog that allows you to refer to it later.

So the query above can be stored as a view using the CREATE VIEW statement as follows:

Examples :Now we have created View with emplyee_info name we can use like a table to fetch data

Query:

CREATE VIEW employee_info AS

SELECT EmpID, FullName, job, Salary

FROM emp

We can view data same as table query here we are fetching Emplyee having less than 4000 salary from view.

Query:

select * from employee_info where Salary<4000

In above example I have taken data from from the single table but a view may consist of columns from multiple tables using joins or just a subset of columns of a single table. This makes views useful for abstracting or hiding complex queries.

Uses of a View :
A good database should contain views due to the given reasons:

1. Restricting data access –
   Views provide an additional level of table security by restricting access to a predetermined set of rows and columns of a table.
2. Hiding data complexity –
   A view can hide the complexity that exists in a multiple table join.
3. Simplify commands for the user –
   Views allows the user to select information from multiple tables without requiring the users to actually know how to perform a join.
4. Store complex queries –
   Views can be used to store complex queries.
5. Rename Columns –
   Views can also be used to rename the columns without affecting the base tables provided the number of columns in view must match the number of columns specified in select statement. Thus, renaming helps to to hide the names of the columns of the base tables.
6. Multiple view facility –
   Different views can be created on the same table for different users.

STORED PROCEDURE

A stored procedure provides an important layer of security between the user interface and the database. It supports security through data access controls because end users may enter or change data, but do not write procedures. … It improves productivity because statements in a stored procedure only must be written once.

To create a stored procedure that wraps this query, you use the CREATE PROCEDURE statement as follows:

Query :

CREATE PROCEDURE storeProcedure_Emp

AS BEGIN

SELECT EmpID, FullName, Salary

FROM emp

ORDER BY EmpID;

END;

In this syntax:

• The storeProcedure_Emp is the name of the stored procedure.
• The AS keyword separates the heading and the body of the stored procedure.
• If the stored procedure has one statement, the BEGIN and END keywords surrounding the statement are optional. However, it is a good practice to include them to make the code clear.

You can find the stored procedure in the Object Explorer, under Programmability > Stored Procedures as shown in the following picture:

Executing a stored procedure

To execute a stored procedure, you use the EXECUTE or EXEC statement followed by the name of the stored procedure:

Query:

Exec storeProcedure_Emp

STORED PROCEDURE VS VIEW

First you need to understand, that both are different things. Stored Procedures are best used for INSERT-UPDATE-DELETE statements. Whereas Views are used for SELECT statements. You should use both of them.

A SQL View is a virtual table, which is based on SQL SELECT query. A view references one or more existing database tables or other views. It is the snap shot of the database whereas a stored procedure is a group of Transact-SQL statements compiled into a single execution plan.

View is simple showcasing data stored in the database tables whereas a stored procedure is a group of statements that can be executed.

A view is faster as it displays data from the tables referenced whereas a store procedure executes sql statements.