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

SQL Expressions

Practical Learning: Creating Functions



In the Object Explorer, right-click RealEstate1 and click New Query...
To create a function, type the following statement:
CREATE FUNCTION CalculateWeeklySalary()
RETURNS Decimal(8, 2)
AS
BEGIN
RETURN 880.44
END;
GO


To execute the statement, on the SQL Editor toolbar, click the Execute button
To save the file that contains the code of the function, on the Standard toolbar, click the Save button
Type Calculate as the name of the file
Click Save
In the Object Explorer, expand the RealEstate1 node, expand Programmability. Expand Functions. And expand Scalar-Valued Functions. Notice the presence of the CalculateWeeklySalary node
Function Calling



After a function has been created, you can use the value it returns. Using a function is also referred to as calling it. To call a function, you must qualify its name. To do this, type the name of the database in which it was created, followed by the period operator, followed by dbo, followed by the period operator, followed by the name of the function, and its parentheses. The formula to use is:

DatabaseName.dbo.FunctionName()
Because a function returns a value, you can use that value as you see fit. For example, you can use either PRINT or SELECT to display the function's value in a query window. Here is an example that calls the above Addition() function:

PRINT Exercise.dbo.GetFullName();
As an alternative, to call a function, in the Object Explorer, right-click its name, position the mouse on Script Function As, SELECT To, and click New Query Editor Window.

Practical Learning: Calling a Function



In the Object Explorer, right-click RealEstate1 and click New Query
To execute the function we just created, execute the following statement:
PRINT RealEstate1.dbo.CalculateWeeklySalary();
GO






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