User Defined Function versus Stored Procedure

User Defined Function versus Stored Procedure

We are going to show you the difference between a stored procedure and a user defined function in SQL so that you can get on the right path in starting your scripting skills.

This article is based on my video What’s the Difference Between a Stored Procedure and User Defined Function?

Is there a difference between the stored procedure and the user-defined function? Great question! They do have similar functions, but let’s dig deeper and differentiate the two. As mentioned, the purpose and use of the two functions are quite the same, but there are a few distinctions to separate each other.

What’s the Difference Between a Stored Procedure and User Defined Function in SQL?

Edited Transcript:

Typically, you are going to use a stored procedure when you want to work with large things that would code, like processes that would update tables and other elements of the database.

You would use a function if you are looking to take maybe two parameters and do a quick calculation on those parameters and get a result, or perhaps just a quick little table of results.

The main difference is that the idea is a user-defined function is going to stand on its own while the stored procedure can run all command that affects the database. So, if you are planning on doing something that is going to transform your information in your system, you are probably looking to use a stored procedure.

However, if you are looking to reduce some complexity, expression, or calculation, then a user-defined function is used.

Comparison of SPROC to UDF

Now, let’s quickly look into the differences between the two from a mechanical point of view and see how they compare with each other.

In a stored procedure, it will return the maximum date. On the other side, there is a similar function, but it is a user-defined function to get the maximum date.

So, when declaring these stored procedures versus UDFs (even with using shortcuts), they are quite similar.

Let’s say, create SPROCS like max date, or whatever name you would want to use and you declare the parameters that are brought into your procedure. In this case, date1 and date2 – do you know the output? We’ll get back to this in a bit.

Defining Stored Procedures and Functions

Under the user-defined function, let’s create the function. There’s that quick difference – not big, but a small one and then a name. Let’s give it a different name so we want them to collide. Let’s then specify the parameters. Notice a slight difference or the similarity? But take note of the parenthesis, there is a slight syntax difference, but not much.

Now let’ get to the nitty-gritty parts. You are going to see that there’s a little difference here because we will be using the same statements as the if-then. But we need to work around how they return the data. We will focus on that in the next step.

However, the bottom line, if you look you can see it does a comparison, and then if one value is greater than the other it all set a return value. Otherwise, it sets the other return value.

It’s like, “Hey, date1 is greater than date2, alright. So the maximum is going to be date1. Otherwise, the maximum is date2.

So, if you look in the figure below, it is doing the same thing, “hey, date1 is greater than date2, so my return value is date1.” Otherwise, it is going to be date2, and then it returns up. Which brings us to the next thing and that is how do they return values?

Here’s a key difference. In a stored procedure, you define another parameter and then you just specify it as an output.

You will see our parameter list and will tack it at the end. We will call this, returned value, but it could have been anything. We will give this a data type and then saying output and that just means that whatever value goes into this parameter, can be reachable within the scope of the script that called it. This is how we are going to get the results outside that stored procedure.

In the previous section, we had set this up as we are going through the program. I would set retval to whatever value I wanted to return and then when the stored procedure would end, it is going to expose the return value. This functions a little differently, how this works is that when you create the function definition, you tell what it returns. You will go, “all right, my function return is a date.” So, it returns a date. As you work through the function, the last statement in your user defined function is a return command and then you return a value which would be a date, and that is going that gets returned when the max dates call.

Calling a Stored Procedure and User Defined Function

You can see we actually have to set up a temporary holding value for our return and then as we go through our logic, we put our date into that holding value and then at the very end. Let’s command it to return the holding value, and that is what gets returned.

You may be asking right now, “okay, how does all this stuff gets called?” Good question! Here is one of the biggest differences that we can think of, that you want to look at is how things are called.

With a stored procedure (notice how we stressed the stored procedure), it’s pretty much command in a line onto itself. We declare the return value as a date and then literally execute the stored procedure and bringing into dates to compare, and then as it does the comparison. This is going to send back the maximum date into the return value, then I will be able to select it.

What is interesting is that we have to declare this as our output on both the calling functions and the decoration functions of the stored procedure. It is quite interesting that way. When we do select this, we will see the maximum value. In this case, it would be May. Now that we have a user-defined function, look just how simple it is for this to get called. It’s as if this is built to be in line with the select statement – which it is.

Here, I have a sweet select statement and we are saying, “Call my user-defined function, and you will see how much simpler it is to get called. Here are our two parameters – April 23rd and May 2nd. Which one do you think is greater? Boom!

What this does is come back and display May 2nd  as the greatest value. This is almost acting like a built-in function.

If you were to look at what you can do and call would be stored procedures, then you are going to find that in a user-defined function – you can call everything. You will find that stored procedures pretty much have the capacity to call all the commands. In fact, they can call commands to do security, update tables, all the deterministic and non-deterministic functions. They can call random things like that. However, a user-defined function has restrictions. So, in this article, we will show the key differences that are going to get you in trouble because you are going to go – this would be great because it would be easier to find function. As you start to write it out, the database will yell at you and go, “You know what, you’ve tried to use the random function, which is non-deterministic and you are not allowed to do that.”

What are the statements we can use as a new user to find a function? DECLARE- setting up variables, assigning values to variables. That’s good though and surprisingly we can use cursor operations and a user-defined function. This is not recommended, but this can still be used. You can only use control of flow such as if, then, began, while, and etc. – these types of statements. But amazingly, you cannot use TRY-CATCH. You would think that you would be able to do some air trapping in there, but as of today, you cannot do this type of air strapping, You have to implement some old-school air trapping using the on-air go to.

You can apply the SELECT statement and some UPDATE, INSERT and DELETE statements if you are trying to modify table variables that are local to your user-defined function. This way, you can do things to set up temporary table variables to build a result and then return that result as a table variable because a table variable type could be one of your return variables.

So, if you think about what you can do as an allowed statement user-defined function, you will see that they’re cool. They can help you, but they are restrictive.

In conclusion, there are a couple of things we want to summarize. First of all, we would use user defined functions in SQL to replace the complicated expressions in our queries and avoid any repeating complicated code. They can also help in cleaning up the code and promote consistency. And if you have this nasty expression that calculates the end of the fiscal year, that is probably a perfect candidate to turn into a user-friendly function.

We will use stored procedures when we want to automate a process. Let’s update one or more tables then wrap things into a transaction and if something doesn’t work or it comes out with errors with a try catch-that-you-can rollback or we can commit, use a stored procedure.

We want to do data transformations where we got a series of steps where we are pulling the data through and resolve as we got transformed data that maybe what we are putting into a dimension or a fact table – this is a good candidate for a stored procedure. When we want to schedule database activities, stored procedure, what we want to use for that or create utilities that use security functions because we are not going to be able to do those in the user-defined functions.

Basing on what we had done here, the user-defined functions are really good to help you minimize the complexity of complex expressions in your select statement. Otherwise, we’re probably going to be using a stored procedure.

For questions or opinions, write us a note through this comment and let’s have a discussion.