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

SQL Expressions

Function Maintenance


Introduction



Because a function in Transact-SQL is treated as an object, it may need maintenance. Some of the actions you would take include renaming, modifying, or deleting a function.

Renaming a Function



If you create a function and execute it, it is stored in the Scalar-Valued Functions node with the name you gave it. If you want, you can change that name but keep the functionality of the function.

To rename a function, in the Object Explorer, right-click it and click Rename. Type the desired new name and press Enter.

Deleting a Function



If you create a function and decide that you don't need it any more, you can delete it.

To delete a function in the Object Explorer, locate the function in the Functions section, right-click it and click Delete. The Delete Object dialog box would come up. If you still want to delete the function, click OK; otherwise, click Cancel.

To programmatically delete a function:

In a query window, type DROP FUNCTION followed by the name of the function and execute the statement
In the Object Explorer, right-click the name of the function, position the mouse on Script Function As, DROP To, and click New Query Editor Window
Open a new query window associated with the database that contains the function. Display the Templates Explorer and expand the Function node. Drag the Drop Function node and drop it in the empty query window
Practical Learning: Deleting a Function



In the Object Explorer, under the Scalar-Valued Functions node, right-click dbo.CalculateWeeklySalary and click Delete
In the Delete Object dialog box, click OK
Modifying a Function



As mentioned already, in the body of the function, you define what the function is supposed to take care of. As a minimum, a function can return a simple number, typed on the right side of the RETURN keyword. Here is an example:

CREATE FUNCTION Addition()
RETURNS int
BEGIN
RETURN 1
END
You can also declare new variables in the body of the function to help in carrying the assignment. A variable declared in the body of a function is referred to as a local variable. Once such a variable has been declared, it can be used like any other variable. Here is an example:

CREATE FUNCTION Addition()
RETURNS int
BEGIN
DECLARE @Number1 int
SET @Number1 = 588
RETURN @Number1 + 1450
END
Practical Learning: Declaring Local Variables



In the Calculate query window, change the code as follows (notice the addition of the schema):
CREATE FUNCTION Payroll.CalculateWeeklySalary()
RETURNS Decimal(8, 2)
AS
BEGIN
DECLARE
@HourlySalary Decimal(8, 2),
@WeeklyHours Real,
@FullName varchar(100);
SET @HourlySalary = 24.15;
SET @WeeklyHours = 42.50;
RETURN @HourlySalary * @WeeklyHours
END;
GO


Press F5 to execute the statement






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