Most relational databases - including MySQL, MariaDB, and SQL Server - support stored procedures and functions. Stored procedures and functions are actually very similar, and can in fact be utilized to accomplish the same task. That being said, there are some crucial differences between the two that need to be considered when deciding which to use for a given job. We'll go over these in today's blog
Stored Procedures
A stored procedure - or "proc" for short - is a set of Structured Query Language (SQL) statements with an assigned name, which are stored in a relational database management system as a group, so it can be reused and shared by multiple programs. Stored procedures can access or modify data in a database, but it is not tied to a specific database or object. This loose coupling is advantageous because it's easy to reappropriate a proc for a different but similar purpose.
Stored procedures can accept input parameters and return multiple values of output parameters; moreover, stored procedures can program statements to perform operations in the database and return a status value to a calling procedure or batch.
Finally, stored procedures can execute multiple SQL statements, call functions, and even iterate over results sets, performing complex operations akin to programming code. When completed, the proc typically returns one of more result sets to the calling application.
User Functions
A function is similar to a stored procedure in that it contains a set of SQL statements that perform a specific task. The idea behind Functions is to foster code reusability. If you have to repeatedly write large SQL scripts to perform the same task, you can create a function that performs that task so that, next time, instead of rewriting the SQL, you can simply call that function. Databases typically include a set of built-in functions that perform a variety of tasks, so always take a look at these before writing your own.
A function accepts inputs in the form of parameters and returns a value. Unlike a stored procedure, a function cannot return a result set. Moreover, functions cannot modify the server environment or operating system environment.
Main Differences
While both procs and functions can be employed in similar ways, functions are designed to send their output to a query or SQL statement. Meanwhile, stored procedures are designed to return outputs (i.e. one or more result sets) to the application.
Another difference is that you can group a set of SQL statements and execute them within a stored procedure, stored procedures cannot be called within SQL statements. Functions, on the other hand, may be invoked directly from your queries and/or stored procedures.
Finally, a limitation of functions is that they have to be called for each row. Therefore, if you are using functions with large data sets, you can encounter performance issues.
Viewing Stored Procedures and Functions in Navicat
In Navicat database management and development tools, you'll see both procs and functions under "Functions". The stored procedures have the "Px" prefix, while functions have an "fx":
Conclusion
Stored procedures and functions are very similar in many ways, but each serve a different purpose. You can think of a stored proc as a grouping of SQL statements, while a function takes input and returns an output value based on the input parameters.
