Scalar user defined functions

1. Scalar user defined functions

It's time to dive into the world of user defined functions!

2. User defined functions (UDFs)

What is a user defined function or UDF? It's a routine that can accept input parameters, perform an action, and return a result. The result can either be a single scalar value or a table result set. Why would we choose to create a user defined function? SQL can execute the function faster since it is pre-compiled and an execution plan exists beforehand. A UDF can also reduce network traffic depending on how it's executed. These potential improvements depend greatly on how the function is created. In some cases, performance can actually be negatively impacted, and we will address this later. User defined functions are also a modular programming tool.

3. What is modular programming?

Modular programming is a software design technique that emphasizes separating functionality of a program into independent, interchangeable modules. You can reuse these independent modules in various queries, but you only need to maintain them in one place. They can also improve code readability.

4. Functions in recipes

Here is the recipe for my favorite cookies. There are ingredients listed at the top followed by instructions. Take a closer look at the 5th instruction, bake at 350 degrees for 7 to 10 minutes. What does this instruction really mean? It's actually a function. It's comprised of 7 different steps that need to happen. Our brain interprets this function and knows the detailed steps to execute.

5. Bake function input parameters

Some of the steps within the Bake function happen the same way each time like press bake button and press start button. Some of the steps are affected by the variable values of 350 degrees and the 7 to 10 minute time range. These are the inputs that need to be passed to the Bake function. If the temperature or time isn't specified, the Bake function can't be executed. These are the input parameters of the function. The input parameter values can change each time the Bake function is executed. If we get a new oven and the Bake function needs to be modified we can make changes without updating each recipe. Conversely, all of the recipes that use the Bake function will be updated as well.

6. Scalar UDF with no input parameter

Now let's learn the SQL syntax required to create a UDF. This code will create a user defined function named Tomorrow. It doesn't accept input parameters, which is indicated by the empty parentheses following the function name. Next, the RETURNS keyword describes the single scalar data type that will be returned. The BEGIN END keywords are control flow statements, and the code enclosed are executed together. Within the BEGIN and END, the RETURN keyword identifies the output. Here DATEADD is executed to add one day to the current date.

7. Scalar UDF with one parameter

Here is a UDF that has one input parameter named @DateParm and is a date data type. This function returns a numeric value which is the SUM() of all the trip durations with a PickupDate equal to the input parameter value. All user defined function names should contain a verb and parameter names must begin with an at sign.

8. Scalar UDF with two input parameters

This UDF named GetRideHrsDateRange has two input parameters, @StartDateParm and @EndDateParm, which are both datetime data types. The UDF returns a numeric value which is the total ride hours for trips with a Pickup Date that is greater than the StartDateParm and less than the EndDateParm. The BEGIN END keywords are used again and must be in every scalar user defined function.

9. It's your turn to create UDFs!

Now, let's get some practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.