Domesticated Brain

SQL Stored Procedures 

 

What is a stored procedure?

A stored procedure is a group of SQL(Structured query language) statements with a defined name and stored in the database to be used by any related application.

Think of a situation that we use same piece of sql statements again and again. We can store these sql statements in the database as a procedure

In following example we are working with the student table and course table

SQL Stored Procedure Toutorial Student tableSQL Stored Procedure Tutorials Course Table

As an example now we need to create a sql procedure to find out what is the course followed by each student

Here is the sql query to get the required data in the database

SELECT student.FirstName, student.LastName, course.CourseTitle, course.Duration
FROM student INNER JOIN
course
ON course.courseID = student.course_id
ORDER BY student.FirstName

 

Let’s see how we can use this query as a stored procedure

CREATE PROCEDURE  SelectedCourse

AS

SELECT student.FirstName, student.LastName, course.CourseTitle, course.Duration
FROM student INNER JOIN
course
ON course.courseID = student.course_id
ORDER BY student.FirstName

 

Stored procedures can be executed using following statement

EXEC  SelectedCourse

 

Out put of the above procedure as follows

SQL Stored Procedure Toutorial Out Put

 

Advantage of using stored procedures

The main advantage of using stored procedure is helping to improve speed and the efficiency of the application. If simply defined it leads to better performance of the application.

Normally there are plenty of sql statements to be executed during the run time of the application. These groups of sql statements can be executed much efficiently as a stored procedure rather than executing in the client machines. Because stored procedures are located on the server and also as we discussed before stored procedures can execute by one single call statement.  

Increase maintainability and simplify the testing process of an application

Sometimes stored procedures can be used in number of applications. When changes are required we can edit and validate the procedure only in the server without changing and validating on every single client machine.

Increase security

No need to spent time to improve security on application code. Can control granted permissions for users by DBMS

Reduce network traffic

When we used stored procedures we don’t have to send the query over the network because the required queries are included in the procedures which are stored in the database

If you have found  what you expected please comment and like on our facebook page