There are three types of UDF (refer to reference), however I will talk about Scalar UDF  here (Which means that function only return single value, you may refer to reference links below for other types).

UDF is quite useful for sometimes, ok, lets go to the point:

Environment:

1. MS SQL Server 2000 or above.
2. Northwind sample database from Microsoft.

Objective:

• List all products in ONE row according to supplier id.

You can try it without UDF, do post your method if you know how to do that.

Here is the part of Product data:

Northwind – Products

First: We need to create a function to concat all product names into one line for each supplier id.

USE NORTHWIND
go

CREATE FUNCTION dbo.ConcatProductName (@sSupplierID as varchar(5))
RETURNS VARCHAR(1000)
AS
BEGIN

   DECLARE @sProduct as varchar(500)

   SET @sProduct = ''

   SELECT @sProduct = @sProduct + ProductName + ', ' FROM PRODUCTS
   WHERE SupplierID = @sSupplierID
   --	group by ProductName

   SET @sProduct = Left(@sProduct, Len(RTrim(@sProduct)) - 1)

   RETURN @sProduct
END

Second: Run and create the function above.

Third: Run the query below the check the result:

Select dbo.ConcatProductName(SupplierID) as ProductName

, SupplierID

From Products

Group by SupplierID

-- drop function ConcatProductName

Query with UDF

See? It work great.

Refer to references below to know pros & cons, and more details about UDF.

References:

Northwind & pubs sample database from Microsoft.

User Defined Functions in Microsoft SQL Server 2000

User Defined Functions

Keywords: Customize function in MSSQL