SQL Server logo

SQL Server User-defined Functions

So, in plain English, what is a T-SQL user-defined function? A function is a SQL query saved as a database object, which leverages and outputs data to serve a focused purpose. Once a function is created it easily can be utilized at any time by anyone with proper access. Measured creation and execution of functions will greatly streamline data production from your T-SQL environment, allowing technology to do what it’s supposed to - make our lives easier! Let’s start with a simple example to better explain why we should be using functions and illustrate different types.

This is part 1 of a 2-part blog.

Calculating a patient’s age seems simple enough. MEDITECH even has fields in the Data Repository for a patient’s current age. But there are many instances where you may be combining patient data from other sources where only a date of birth is readily accessible. Alternatively, you may come across reporting needs that require data based on how old a patient was or will be at a certain point in time. There are two ways we can use TSQL functions to accommodate these needs: scalar functions and table-valued functions.

Scalar Functions

A scalar function returns a single value each time it is called. We can write a scalar function designed to calculate and return a patient’s age in years as of any date of interest (e.g., admission date, lab test date, etc.) based on their date of birth. So, if you have a data set that contains 10 patients, the function will return each patient’s age as a single field to be used in that data set. Let’s look at an example with a function named fnAgeYears.  (This function can be downloaded for free from the DR Network.)

 blog user defined functions 1

 

Line 38 above shows what value we are returning: the difference in years between the date of birth (DOB) and @CheckDate, our date of interest. However, since we know SQL datetime date units (“year” from our function) are only as specific as their nearest date interval (in this case, January 1 at midnight), we need to be more specific. Our CASE statement on lines 39-41 accomplishes this: it counts to the specific day of the year (the cumulative day number for a 365-366 day calendar) for each date of interest. In addition, this also accommodates leap years, for those folks whose birthdays may fall on February 29.

Table-valued Functions

Unlike scalar functions, table-valued functions return multiple values each time they are called, including multiple rows of values, just like a table. This makes TVFs very flexible in that a single function can be used in a variety of ways, or for different discrete column values, depending on the need. What if we used a variation of our age function to calculate all the discrete components available in a SQL datetime datatype, any one (or more) of which we might use in clinical calculations? Let’s take a look at tvAw_AgeAll. (This function can be downloaded for free from the DR Network.)

First the code:

blog user defined functions 2

 

First note that the syntax on line 46 that indicates we’re returning a table for our results (hence, the name “table-valued.”) From there on, it looks like a lot is happening, but if we look at a sample result from this function, it’s quite simple. To call the function, you simply SELECT from it, just like you would a table. In this case, we’ve selected everything, just to illustrate what’s happening in the code above.

 

blog user defined functions 3

So with one function, we have all the time interval components, from years to minutes, returned from our table-like results, any or all of which can be used in your expression. Taking a step back, while this function was built with a BirthDate parameter, it in fact could be more generic to be any two dates, since we’re capturing time intervals between two datetime expressions.

Conclusions

Hopefully you can see how user-defined functions are flexible and useful, and ultimately can save you coding time. In part 2 of this blog, we’ll discuss some other advantages and best practices around user-defined functions.


Edited by Ian Proffer