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.)

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:

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.

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