Stored Procedures - An Introduction

Stored Procedure

A stored procedure is a set of TSQL statements that can be compiled as a single execution plan.

Properties

A stored procedure is registered at the server (in common language, resides in the server).

A stored procedure may or may not return output result set. Result set could be tabular or scalar value.

A stored procedure can return messages to the client; invoke DDL and DML statements and return output parameters.

A stored procedure may or may not have parameters.

A stored procedure can be used inside other stored procedures called nested procedures but cannot be used in user-defined functions.

Advantages

* Performance enhancement

Since the execution plan can be re-used for subsequent executions, performance can be greatly enhanced.

 

* Allows Modular Programming

Instead of re-writing all the logic every time required, it can be embedded into a stored procedure and can be executed in a single line. It improves the modularity of the code.

Any change to the logic can done at a single location (stored procedure) and it can reflect where ever it is called. A stored procedure can contain any amount of logic in it.

* Reduce network traffic

Network traffic is reduced when a single execution line is sent to the server instead of the huge code.

The execution plan created during the execution of a stored procedure can be re-used in the subsequent executions.

* Security Constraints

Stored procedures can have security attributes such as permissions, ownership etc. associated with them

***Users can be granted permission to execute a stored procedure without having to have direct permissions on the objects referenced in the procedure.

* Prevent SQL Injection Attacks

Parameterized stored procedures (stored procedures with parameters) can help to prevent SQL Injection attacks on the applications.

***SQL Injection means direct insertion of malicious code into the application through user-input variables. It can never be detected as long as no syntax error occurs.

Types

There are three types of stored procedures.

User-Defined - Transact-SQL and CLR Stored Procedures

Transact-SQL SPs are those to which all the above discussed points are applicable.

CLR stored procedures are actually a reference to MS .Net Framework Common Language Runtime that can take and return user-supplied parameters. It allows creating .Net assemblies in the database.

Extended Stored Procedures

 These are actually the DLLs that SQL Server can dynamically load and run. They are programmed using Extended Stored Procedure API. They allow us to create our own routines in programming language like C.

***This feature will be deprecated in the future versions of SQL Server 2008. So, do not use.

System Stored Procedures

 System stored procedures are used to perform many administrative activities in SQL Server. They are physically stored in "Resource" database and have a prefix "sp_".

Syntax

CREATE PROCEDURE dbo.ProcedureName (@parametername datatype)

AS
BEGIN

<<<<<<<  Business Logic  >>>>>>

END

GO

Notes:

*Parameters are three types.

Input – Used to give input values

Output – Used to capture output values

In-Out – A parameter that can act be used to give input value and capture output value

* Maximum number of parameters in SQL Server 2005 and 2008 is 2,100.

* Maximum nested stored procedure levels – 32

Points to keep in mind when writing a stored procedure

Writing an optimized stored procedure is as important as writing the correct logic in the stored procedures. Here are some tips.

* Never use * in the SELECT statement. Retrieve only required columns as it reduces the network traffic.

* Try to use temp tables where a very huge sub-query is required.

* Avoid using cursors by making use of the temp table and WHILE loop.

* Make use of the indexed columns in the filters

* Make best choice in selecting temp table or table variable