Changing the Structure of User-Defined Functions
The Transact-SQL language also supports the ALTER FUNCTION statement, which modifies the structure of a User-Defined Function. This statement is usually used to remove the schema binding. All options of the ALTER FUNCTION statement correspond to the options with the same name in the CREATE FUNCTION statement.
A UDF is removed using the DROP FUNCTION statement. Only the owner of the function (or the members of the db_owner and sysadmin fixed database roles) can remove the function.
User-Defined Functions and CLR
The discussion in “Stored Procedures and CLR” earlier in the chapter is also valid for UDFs. The only difference is that you use the CREATE FUNCTION statement (instead of CREATE PROCEDURE) to store a UDF as a database object. Also, UDFs are used in a different context from that of stored procedures, because UDFs always have a return value.
Example 8.25 shows the C# program used to demonstrate how UDFs are compiled and deployed.
using System;
using System.Data.Sql;
using System.Data.SqlTypes;
public class budgetPercent
{ private const float percent = 10;
public static SqlDouble computeBudget(float budget)
{ float budgetNew;
budgetNew = budget * percent;
return budgetNew;
}
};
Code language: PHP (php)
The C# source program in Example 8.25 shows a UDF that calculates the new budget of a project using the old budget and the percentage increase. (The description of the C# program is omitted because this program is analog to the program in Example 8.13.) Example 8.26 shows the CREATE ASSEMBLY statement, which is necessary if you want to create a database object.
USE sample;
GO
CREATE ASSEMBLY computeBudget
FROM 'C:\computeBudget.dll'
WITH PERMISSION_SET = SAFE
Code language: PHP (php)
The CREATE FUNCTION statement in Example 8.27 stores the computeBudget assembly as the database object, which can be used subsequently in data manipulation statements, such as SELECT, as shown in Example 8.28.
USE sample;
GO
CREATE FUNCTION ReturncomputeBudget (@budget Real)
RETURNS FLOAT
AS EXTERNAL NAME computeBudget.budgetPercent.computeBudget
Code language: PHP (php)
USE sample;
SELECT dbo.ReturncomputeBudget (321.50)
Code language: CSS (css)