Stored Procedures vs. Functions: The right tool for the job
When working on or developing a database, you’re akin to a craftsman selecting the perfect tool from a well-organized toolbox. In the realm of data, we have at our disposal a vast array of tools, but it’s our decision that determines what, when, and how we use them. But in this article, we’ll talk about 2 shining tools in your backpack: stored procedures and functions.
Curious? Don’t waste another moment and embark on a new and thrilling adventure, with this article as your map.
Open our backpack
This time your arsenal is composed of two anonymous tools of data manipulation, which are responsible for adding an extra layer of functionality and logic, making our trip more dynamic.
Similar to any craftsman, you have to know your tools, discern the distinctions between them, and recognize when you need to use one over the other.
What is a stored procedure (SP)?
There are times when you have complex and quite long statements that are used to perform different tasks weekly or even daily. Just imagine what it means to run the script manually even if it’s a sunny Sunday. Thank God we’re just imagining this 😅
It’s well known that:
A stored procedure in SQL is a group of SQL statements that are stored together in a database. Based on the statements in the procedure and the parameters you pass, it can perform one or multiple DML operations on the database, and return value, if any. — SimpliLearn
Some of the key characteristics:
- Performance optimization: the execution of a stored procedure is faster because it makes use of the database’s optimization strategies. A stored procedure is compiled, optimized, parsed, and stored in the database cache.
- Security: some time ago, we talked about using stored procedures to avoid SQL injection (can find the article here 😊). Well, to tighten restrictions and take advantage of this characteristic we can use parameters.
- Modularity: why would you write a 100-line SQL statement in your application’s code when you can use a stored procedure? If the requirements change, you’ll only need to have a short meeting with the stored procedure in charge, not the ENTIRE app’s code.
- Reusability: Do the work once and use it infinitely because stored procedures can be called multiple times from different parts of an application or by multiple users.
- Reduced bandwidth: As i mentioned in the first key characteristic, a stored procedure is stored in the database cache, so its execution “eats” less bandwidth because it’s executed from there.
What is a function (UDF)?
Or better say, user-defined functions (because in SQL are various types of functions) are:
routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set. — Microsoft Learn
Let’s see some key characteristics of UDFs:
- Return value: a user-defined function always has a returned value, regardless of the data type the database system supports.
- Function overloading: you can create UDFs with the same name, but different parameters. You might think that using the same name will overwrite your function, but in some database systems that is not the case; you’ll find both or all functions with the same name, the difference is the parameter list.
- Multiple types of UDFs: We have different types of UDFs such as scalar functions, table-valued functions, and aggregate functions (we will talk more about these types in a future article).
I still remember when a colleague came to me and said he needed a procedure that returns a table. At first, i had no reaction, but after a few seconds, i told him i could not help him, not with a procedure, but with a function (i upset him a little when i said that i couldn’t help him, at least not with a SP, sorry 😅)
2 tools and many differences
Is there an unknown magic hand that chose the right tool for your needs or something? If such a thing exists, i’d love to have it too. Anyway, to be able to take the perfect approach to your specific task, you need to understand the differences between the components in your arsenal.
What use when?
Based on the table above, we know that we have 2 options:
Chose to go with a stored procedure (SP) when:
- you need to perform various DML (Data Manipulation Language) operation on your data (like inserts, updates, and even delete)
- you need a reusable piece of code to fulfill a specific task
- you need to combat SQL injection and utilize dynamic SQL
On the other hand, opt for a user-defined function (UDF) when:
- you need to perform data transformation operations, including calculations, formatting, and conversions, and return a result
- you need an object that can be called as part of a SQL statement and must return a value to be used in the statement.
In conclusion
Throughout this article, we have seen that the two tools provided by the database system are more than just life savers. One of them can save you from the daunting task of rewriting multiple pieces of code and protect you from one of the most feared enemies of programming, SQL injection. The other tool helps you simplify your database logic by abstracting complex calculations.
Always remember, that you are the craftsman, and this crucial decision rests in your hands. Armed with the insights from this article, I’m confident that your crafted masterpiece will be more than exceptional.
