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:

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.

About Author

Rashed

I’m Rashedul Alam, software engineer/architect, technical blogger, and educator. I have over 10 years of working experience in the field of software design and development. I like to share my knowledge and technical stuff with others. I am a community contributor, providing problem solving answer through different forum.