Stored Procedure

Introduction

Stored procedures are precompiled database queries that improve the security, efficiency and usability of database client/server applications. Developers specify a stored procedure in terms of input and output variables. They then compile the code on the database platform and make it available to application developers for use in other environments, such as web applications. All of the major database platforms, including Oracle, SQL Server and MySQL support stored procedures. The major benefits of this technology are the substantial performance gains from precompiled execution, the reduction of client/server traffic, development efficiency gains from code reuse and abstraction and the security controls inherent in granting users permissions on specific stored procedures instead of the underlying database tables.

A stored procedure is written using Transact-SQL (T-SQL). T-SQL includes variables, conditional logic, loops and flow control. A stored procedure allows you to put code or business logic on the database server.

A stored procedure allows for more modular programming. You can create reusable, discrete pieces of functionality using stored procedures. Stored procedures have the parsing and execution plan built at the time they are created so they execute faster than dynamic SQL. If you have a stored procedure that manipulates large amounts of data, that data will remain on the server and not be transported across the network. You can also use stored procedures for security. You can grant users execute permissions on stored procedures without granting them permissions on objects manipulated by those stored procedures.

Benefits of Stored Procedures

• Precompiled execution – SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
• Reduced client/server traffic – If network bandwidth is a concern in your environment, you’ll be happy to learn that stored procedures can reduce long SQLqueries to a single line that is transmitted over the wire.
• Efficient reuse of code and programming abstraction – Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you’ll find the development cycle takes less time.
• Enhanced security controls – You can grant users permission to execute a stored procedure independently of underlying table permissions.

A simple stored procedure looks like:

Create Procedure spcustomer
AS
SELECT *
FROM dbo.DimCustomer
WHERE Gender='M'
ORDER BY CommuteDistance

This stored procedure is called “spCustomer”. All it contains is a SELECT statement. All stored procedures that SQL Server provides start with “sp_” and I chose to almost follow this convention for this stored procedure. If you try to call a stored procedure that starts with “sp_” SQL Server will first search the MASTER database before searching the current database.

By default, only members of the ‘dbo_owner’ role and ‘db_ddladmin’ role can create stored procedures. You execute a stored procedure by typing it’s name or using the EXECUTE statement. To execute our stored procedure you can type

Execute spcustomer

This will execute the stored procedure and return the results. If you are calling this procedure from an ASP page (or other client) you can use the EXECUTE statement as you SQL string to execute.