If you are using SQL 2005 and find User Defined Functions helpful, be sure to read my new article over at SQLTeam:
Returning Complex Data from User-Defined Functions with CROSS APPLY
SQL Server User-Defined Functions (UDFs) can return either a single value or virtual tables. However, sometimes we might like for a User-Defined Function to simply return more than 1 piece of information, but an entire table is more than what we need. For example, suppose we want a function that parses a single VARCHAR() containing a street address and returns:
- Street Number
- Street Name
- Unit Number
In other words, it would accept "100 Main St #44" and return 3 distinct, separate values:
- Street Number: "100"
- Street Name: "Main St"
- Unit Number: "#44"
As you can see, each value would have its own label and also potentially its own data type. Other examples would be a function that accepts an email address and returns the username and the domain separately, or a function that accepts a full name and parses it into separate First, Middle and Last values. In general, if a single function call can return multiple pieces of information all at once, it reduces the number of function calls you need to make, resulting in shorter code and allowing you to put more complex business logic into fewer functions.
Stay tuned for more posts soon on CROSS APPLY and OUTER APPLY -- they are very useful features and can be really helpful if you need to transform or clean up denormalized (or just plain messy!) data using User Defined Functions.