Microsoft SharePoint Team Blog
The official blog of the Microsoft SharePoint Product Group

SQL Expressions

Function Creation Fundamentals


There are various ways you can start the creating of a function:

In the Object Explorer, expand the desired database. Expand the Programmatically node. Expand the Functions node. Right-click Scalar-Valued Function and click New Scalar-Valued Function... Sample code would be generated for you. You can then modify to customize it
Open an empty query window. Display the Templates Explorer window and expand the Function node. Drag Create Scalar-Valued Function and drop it in the query window
You can open a new empty query window and start typing your code in it

In Transact-SQL, the primary formula of creating a function is:

CREATE FUNCTION FunctionName()
The Name of a Function



We mentioned already that, in SQL, a function is created as an object. As such, it must have a name. In our lessons, here are the rules we will use to name our functions:

A name will start with either an underscore or a letter. Examples are _n, act, or Second
After the first character as an underscore or a letter, the name will have combinations of underscores, letters, and digits. Examples are _n24, act_52_t
A name will not include special characters such as !, @, #, $, %, ^, &, or *
We will avoid using spaces in a name, with few exceptions
If the name is a combination of words, each word will start in uppercase. Examples are DateHired, _RealSport, or DriversLicenseNumber
Returning a Value From a Function



For a function to be useful, it must produce a result. This is also said that the function returns a result or a value. When creating a function, you must specify the type of value that the function would return. To provide this information, after the name of the function, type the RETURNS keyword followed by a definition for a data type. Here is a simple example:

CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
After specifying the type of value that the function would return, you can create a body for the function. The body of a function starts with the BEGIN and ends with the END keywords. Here is an example:

CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
BEGIN

END
Optionally, you can type the AS keyword before the BEGIN keyword:

CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
AS
BEGIN

END
Between the BEGIN and END keywords, which is the section that represents the body of the function, you can define the assignment the function must perform. After performing this assignment, just before the END keyword, you must specify the value that the function returns. This is done by typing the RETURN keyword followed by an expression. A sample formula is:

CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
AS
BEGIN
RETURN Expression
END
Here is an example

CREATE FUNCTION GetFullName()
RETURNS varchar(100)
AS
BEGIN
RETURN 'Doe, John'
END




source : http://blogs.msdn.com/sharepoint/default.aspx