781.329.4300 ¦ 
In part 1 of this blog, we covered at a high level the basics of T-SQL user defined functions. In part 2 we’ll explore the advantages of leveraging functions, and when to do so.
This is part 2 of a 2-part blog.
Functions, like any other tool, have their uses. In my earlier blog we discussed the two types of functions: table-valued and scalar. Generally speaking, when looking to write a function, we recommend table-valued functions, since they usually perform more efficiently (run faster) and handle large datasets very well. While scalar functions only return a single value, table-valued functions can return a single value, or multiple values – in other words, multiple rows of data that look and act like table rows (and can be handled accordingly in your code). They are limited however in that they cannot handle complex structured code such as dynamic SQL or call other functions.
When do you write and leverage a function? The best rule of thumb we can offer is to follow the DRY software development principle. If you’re unfamiliar, DRY is an acronym for “don’t repeat yourself.” Wherever possible you never want to re-write code you’ve already written. Functions are the perfect way to mitigate this in your systems, for all developers. This practice also helps to develop coding standards in your hospital. Coding standards ensure uniformity and efficiencies in your results across the board. Perhaps the greatest efficiency this method offers is error correction. If you find an error in the way a function accumulates data, you simply correct the function. Now, every object that calls the function will be in compliance with the change. As opposed to trying to find and update any code reference in your entire corporate library.
Let’s take a look at an example to see how the above principles are practically applied. On the surface, gathering pertinent information about a provider sounds simple. Take a look at this example of a few commonly-used data points for a provider in MEDITECH:
Looks simple enough, but in version 6.1x of MEDITECH, it takes joining eight tables in T-SQL to produce the dataset. Having a function that does this for you whenever this data is required “flattens” out the tables so you don’t have to type all of this out each time this common information is required. Here’s a simplified version of a table-valued function we use to produce the data:
Now when we want to retrieve the provider information, we call our function (and pass it SourceID and ProviderID as parameters) instead of using all that T-SQL code in every stored procedure that you need provider information for.
Table-valued functions can be written as both multi- and single statements. Keep in mind that a single statement, in-line table-valued function will regularly be the most optimized version in terms of efficiency given data sets of all scale.
In conclusion, functions can be leveraged to:
- eliminate coding repetition
- implement coding standards
- “flatten” wide-spread data elements
- create coding efficiencies across the board
- simplify complex code for repeated use