Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Executing UDFs
Scalar UDFs are executed within a SELECT statement by specifying the UDF owner, name, and any parameters. Unlike other database objects, scalar UDFs disallow omitting the owner name, even if the owner of the UDF is the one calling the function.
For instance, a scalar function owned by dbo returning the date and time in Denver could be executed as follows:
SELECT dbo.udf_Denver_datetime()
Notice that you can use the output of scalar UDFs in the WHERE clause of queries as well. For instance, the following query could take advantage of the same UDF to return the books sold within the past year in Denver:
SELECT
Store_id,
Order_num,
Order_date,
Quantity
FROM
Sales
WHERE
order_date < DATEADD(YEAR, -1, dbo.udf_Denver_datetime() )
AND
City_sold = 'Denver'
In-line and multi-statement UDFs are also executed within a SELECT statement. They also require specifying the owner, function name, and any parameters. The difference is that in-line and multi-statement functions return a rowset; therefore, you can either specify the column list or return all columns from these UDFs. An in-line or multi-statement function owned by user JohnDoe that returns a list of best-selling books can be executed as follows:
SELECT * FROM JohnDoe.udf_best_selling_books()
Alternatively, we can select only those columns that we want to see in the output from the same UDF, as follows:
SELECT title_id, author_name FROM JohnDoe.udf_best_selling_books()
Because in-line and multi-statement UDFs return a rowset, we can join their output to tables, views, or to output of other UDFs (or to any other rowset). For instance, suppose that the udf_best_selling_books contains only the title_id, not the title itself. We can easily join the output of this UDF to the titles table, as follows:
SELECT a.title_id, b.title FROM JohnDoe.udf_best_selling_books() a INNER JOIN titles b ON a.title_id = b.title_id