In the SQL Server 7 days, when we wanted to calculate data and return it, we usually used stored procedures. User-defined functions are a new feature of SQL Server and they are much better as their results can be used in the ‘From’ clause of a ‘Select’ statement. Imagine that you wanted to retrieve data from the Shoes or Shirts tables in order to determine their stock in a flexible way.

 Create Function SomeTable(@Name char(6))Returns @recordsExtracted Table (quantityInStock)AsBeginIf @Name='Shoes' ThenInsert @recordsExtracted Select quantityInStock From ShoesIf @Name='Shirts' ThenInsert @recordsExtracted Select quantityInStock From ShirtsReturnEnd

As you can see, logic and returned recordset in one place. To use this function, you would do the following:

 Select Sum(quantityInStock) From SomeTable('Shoes') , or,Select Sum(quantityInStock) From SomeTable('Shirts')