What is a SQL stored procedure?

A SQL 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

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 the following statement.

EXEC  SelectedCourse

Output of the above procedure as follows

Advantages of using SQL stored procedures

The main advantage of using stored procedures 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 be executed by a 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 use SQL 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