User-Defined Functions

As you have seen, a stored procedure takes a set of data, completes the work as required, and then finishes. It is not possible to take a stored procedure and execute it within, for example, a SELECT statement. This is where user-defined functions (UDFs) come into play. There are two methods of creating UDFs: through T-SQL and through .NET. However you create them, stored functions take a set of inputs and produce output that a query invoking a particular function can further use. UDFs are similar to stored procedures, but it is their ability to be used within a query that provides their power. You have already seen a few system-defined functions within this book, including GETDATE(), which gets today’s date and time and returns it within a query such as SELECT GETDATE().