Stored Procedure

Stored Procedure is an attractive feature of a relational database that contains some SQL statements and perform predefine task based on creator demand. Most the relational database support stored procedure, but their codes may differ. This article describes an introduction to Stored Procedure (SP), basic overview of SQL stored procedure. Summary of the article:

• What is Stored Procedure (SP)?
• Example of Stored Procedure (SP)
• How we can Create Stored Procedure (SP)?
• How we can Execute Stored Procedure (SP)?
• Advantages of Stored Procedure (SP)
• Disadvantages of Stored Procedure (SP)

What is Stored Procedure (SP)?
SP is a set of SQL statement which is written in database server or database. It can handle complex operation according to input. Its input is called parameter. Any SP may have multiple parameters or not. If we write code in SP rather than client end it is faster. Because SP stay & run inside database server. Many database support SP but its codes may differ according to database server. MSSQL Server, mySQL(vertion 5+), Oracle, DB2 etc support SP. Someone may confused with stored procedure and SQL functions. Because both are SQL statements. But they are not same, there exist some difference between SP and functions.

Example of Stored Procedure (SP)
Lets us consider a table named Students. We want to make a simple stored procedure that will returns all the rows of this table.  A sample examle of a SQL Server Stored Procedure is given bellow:

USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Cybarlab
-- Create date: 
-- Description: Simple stored procedures that returns some data
-- =============================================
ALTER PROCEDURE [dbo].[TestProcedure] 
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 SELECT     StudentID, StudentName, Address
 FROM         Students 

END

This SP is stored in a “TestDB” database and its name is “TestProcedure”. It has a simple select command. It will select three columns (StudentID, StudentName, Address) from the table “Students”. In stored procedure or SP, we can write any type of SQL statements according to our requirements.

How we can Create Stored Procedure (SP)?
Different database system offer different mechanism to create stored procedure. We can use normal SQL statements, graphical environment to create the stored procedure. For example the creation process (steps) of stored procedure in MS SQL Server is given bellow:

• From the Management Studio Write clik on your Database
• Select Programmability
• Select Stored Procedures
• Click write and select New Stored Procedure…
• Or we can create a SP by writing the SQL query

How we can Execute Stored Procedure (SP)?
We can call or execute a stored procedure in different way. Some database system offer graphical environment to execute SP. We can executes it by using SQL statement. A sample execution process using SQL command is given bellow:

EXECUTE ProcedureName

EXECUTE is a built in command which is used to call or run a stored procedure.

Advantages of Stored Procedure (SP)
Stored procedure provides us lot of advantages. The benefits of SQL stored procedures are given bellow:

• It is faster
• It is per-compiled
• It reduce network traffic
• It is reusable
• It can handle complex operation
• It support nested SP
• It’s Security is high. No body can see the code. Because it stay in Database Server

Disadvantages of Stored Procedure (SP)
SQL stored procedures has some disadvantages also. Some drawbacks of stored procedures are given bellow:

• Need expert developer, Because it is so hard to write code
• Debugging is hard
• It is not database independent. Its code may very based on database server

Stored procedure can play a important role in our applications. It has numerous advantages. We should use it as much as possible. But remember its drawbacks.