Difference Between Stored Procedure and Function

The main difference between stored procedure and function is that a stored procedure is a set of SQL statements that can be executed on the RDBMS again and again while a function is a set of instructions written using a programming language that can be executed again and again.

A Relational Database Management System (RDBMS) is a database management system that is based on the relational model. It stores data in databases. Each database consists of multiple tables, which are related to each other. RDBMS helps to store, manipulate and retrieve data in the database easily. A stored procedure can be used on RDBMS. It is a reusable set of SQL statements. On the other hand, a function is a reusable set of instructions written in a programming language that performs a specific task. Programming languages such as C, C++, Python, Java, PHP, Perl etc. allow the programmer to create functions.

Key Areas Covered

1. What is a Stored Procedure
      – Definition, Examples
2. What is a Function
      – Definition, Examples
3. Difference Between Stored Procedure and Function
      – Comparison of Key Differences

Key Terms

Function, Stored Procedure

What is a Stored Procedure

A stored procedure is a set of SQL instructions to perform a task on RDBMS. If there is a set of SQL queries that should be reused again, the programmer can write a stored procedure. Then he can call it and execute it. It is also possible to pass parameters to the stored procedure to perform a task.

Figure 1: Stored Procedure

The syntax of the stored procedure is as follows.

CREATE PROCEDURE procedure_name

AS

sql statement

GO;

The following query is used to execute the procedure,

EXEC procedure_name;

The following SQL statement creates a stored procedure name “SelectStudents”. It selects the records of all students in the Student table.

CREATE PROCEDURE SelectStudents

As

SELECT * FROM Students

GO;

The stored procedure can be executed using the following command,

EXEC SelectStudents;

The stored procedure can also accept parameters. Below SQL statement create a stored procedure that selects Students of the given Name from the Student table.

CREATE PROCEDURE SelectStudents @Name varchar(50)

AS

SELECT * FROM Students WHERE Name = @Name

GO;

The following statement is used to execute the procedure.

EXEC SelectStudents Name= “Ann”;

It will select all the students with the name “Ann” in the Student table.

What is Function

A function is a set of instructions to perform a specific task. Almost all programming languages such as C++, C, Java, and Python allow the programmer to write functions.  A function allows reusing the same set of instructions again and again. Furthermore, it organizes the entire code.

Figure 2: The main function in C

A function has the following syntax.

return_type function_name (parameter list){

            //statements inside the function

}

There can be functions that accept parameters and do not accept parameters. Refer below function.

void  displayMessage(){

            prinf(“Hello World \n”);

}

Above is a simple C function. It does not get any parameters. It simply prints the message “Hello World” to the console. This function does not return any value. Therefore, the return type is void.

A function can also accept parameters. Refer the below example.

int calArea(int width, int length){

            int area = width * length;

            return area;

}

The above is a function named calArea. It gets two integer parameters called the width and the length. A local variable called “area” is created inside the function. It is assigned with the multiplication of two values. Finally, the calculated area is returned. It is an integer. Therefore, the return type is int.

Difference Between Stored Procedure and Function

Definition

A stored procedure is a group of SQL statements that can perform a particular task on the Relational Database Management system.  A function is a self-contained module of code that accomplishes a specific task.

Usage

Furthermore, a stored procedure is used in RDBMS while a function is used in any programming language such as C, C++, and Java etc.

Conclusion

The difference between stored procedure and function is that a stored procedure is a set of SQL statements that can be executed on the RDBMS again and again while a function is a set of instructions written using a programming language that can be executed again and again. In brief, a stored procedure is a reusable set of SQL statements while a function is a reusable set of instructions written in a programming language.

Reference:

1. “SQL Stored Procedures for SQL Server.” W3Schools Online Web Tutorials, Available here.
2. “C Functions.” Www.tutorialspoint.com, Tutorials Point, Available here.

Image Courtesy:

1. “Процедур” By Muug1 – Own work, CC BY-SA 4.0) via Commons Wikimedia
2. “Notepad++ v6.9.2 on Windows 10, with “Hello World” source code in C programming language, with function name autocompletion”By Notepad++ v6.9.2 ScreenshotSelf-photographed, derivative work (Public Domain) via Commons Wikimedia