1. UDFs in action
Now that you know how to create both scalar and table valued user defined functions, let's put them to use.
2. Execute scalar with SELECT
There are a variety of ways to execute user defined functions. In this example, we use SELECT to execute the GetTomorrow() function we created in the first lesson. The dbo dot that precedes the function name is the schema where the function exists. The schema must be specified when executing a UDF. If a schema is not specified when creating the function SQL will automatically assign it the user's default schema. We will discuss schema later in more detail.
3. Execute scalar with EXEC & store result
In this example, we declare a local numeric variable named @TotalRideHrs. Then we use the EXEC keyword to execute the GetRideHrsOneDay() function and assign the result to the @TotalRideHrs variable. The UDF parameter name, @DateParm in this example, needs to follow the function name, as well as the equal sign and the parameter value we want to pass to the function. In this case, the parameter value is 1/15/2017. In the last statement of the query, we are using SELECT to print some static text, followed by the local variable which contains the return result of the UDF.
The total ride hours for 1/15/2017 is 71,626.
4. SELECT parameter value & scalar UDF
Here is another example of how to execute a scalar UDF. We DECLARE a local variable named @DateParm and assign its value as the result of a SELECT statement which identifies the oldest pickup date in the YellowTripData table. Then the @DateParm variable is passed to the GetRideHrsOneDay() function. The SELECT is returning the value of the parameter as well as the result of the function. Notice that, when using SELECT to execute the UDF, the parameter is listed within parenthesis after the function name.
The results of the SELECT statement show that for 1/31/2017 there was 75,519 ride hours.
It is possible to execute a function in a WHERE clause, but it often negatively affects query performance. This should be avoided until you are comfortable analyzing query performance during the development process.
5. Execute ITVF with SELECT & FROM
Since a table valued function returns a table result you can execute it using the SELECT and FROM keywords. In this example, we select the top 10 records from the table value that is returned from SumLocationStats() function. We assign the function's parameter value by listing 1/9/2017 within the parenthesis following the function name. You can specify an order by clause when executing a table valued function, but it's not allowed within the function itself.
Here are the top 10 records from the SumLocationStats() function when passing 1/9/2017 as the parameter value.
6. Execute MSTVF & store results
Even though multi statement table valued functions are constructed differently than inline table valued functions they can be executed the same way. However, in this example, we want to store the function's table value results in a local variable. We use DECLARE to create a table variable with the same table structure as the function's table result.
The INSERT INTO keywords populate the table variable with the top 10 records from the table valued function when passing 1 and 2017 as the parameter values. We also use the ORDER BY clause again, but this is optional.
Finally, SELECT is used to see the data stored in the table variable.
The results show the TripCount and AvgFare for each of the first 10 days in January 2017.
7. Execute MSTVF & store results
The results show the TripCount and AvgFare for each of the first 10 days in January 2017.
8. See your functions in action!
Let's see your functions in action with some practice executing.