T-SQL 101: 29 Calling user-defined functions in SQL Server T-SQL queries

Important concepts in any development are reusing code, and abstraction. Look at the first WHERE clause here:

I’ve asked SQL Server to return rows where the CreditRatingID is the one that has the maximum rating. I might not know how to find the maximum rating but if there is code that finds it for me (i.e. dbo.GetMaximumRating()), I don’t need to know that.

This is an example of a function that retrieves a value, and then I can use the value in my own query, without having to copy all the code that’s needed, and without even having to know how it works.

When a function only returns a single value, we call it a scalar function.

If the function is user-written code (rather than system-supplied code in SQL Server), we call it a scalar user-defined function.

Note: There have been some performance problems over the years that are related to using T-SQL scalar user-defined functions, but we’ll talk about that another day.

Table-Valued Functions

As well as just returning single values, functions can also return entire tables of data.

In the second query in the image above, a table-valued function (TVF) that takes a single parameter (i.e. the StateCode), and returns details of the cinemas in that state. Again I don’t need to know how the code works or what it does. I can just use it.

Once again, there are system-supplied table-valued functions and user-defined table-valued functions.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2019-08-05