User Defined Functions in SQL Server
Most of the SQL Server users I know are big fans of stored procedures, but barely use functions, if ever. Functions are really nice, but I'm not going to go into all the reasons you should love them I'm just going to point out the use I recently made of one. You can write what is essentially a stored procedure to join a bunch of data from disparate tables. You have a single place to maintain this logic. Best of all - you can select out of the results of the function - using filtering and table variables where necessary. In my previous post about Dynamic SQL and SSRS 2000, I referenced the fact that the dynamic query I'd been using to grab valid locations was insufficient. I replaced that with a function to do the appropriate work and now I can build a table variable with valid locations and join my SELECT to the table variable in memory. Super sweet for those of you not used to using functions to return what are essentially datasets.
Read this great article to learn the basics of SQL Server's User Defined Functions