Export (0) Print
Expand All

Creating, Altering, and Removing User-Defined Functions

The UserDefinedFunction object provides functionality that lets users programmatically manage user-defined functions in Microsoft SQL Server. User-defined functions support input and output parameters, and also support direct references to table columns.

SQL Server requires assemblies to be registered within a database before these can be used inside stored procedures, user defined functions, triggers, and user defined data types. SMO supports this feature with the SqlAssembly object.

The UserDefinedFunction object references the .NET assembly with the AssemblyName, ClassName, and MethodName properties.

When the UserDefinedFunction object references a .NET assembly, you must register the assembly by creating a SqlAssembly object and adding it to the SqlAssemblyCollection object, which belongs to the Database object.

To use any code example that is provided, you will have to choose the programming environment, the programming template, and the programming language in which to create your application. For more information, see How to: Create a Visual Basic SMO Project in Visual Studio .NET or How to: Create a Visual C# SMO Project in Visual Studio .NET.

This code example shows how to create and remove a scalar user-defined function that has an input DateTime object parameter and an integer return type in Visual Basic. The user-defined function is created on the AdventureWorks database. The example creates a user-defined function, ISOweek, which takes a date argument and calculates the ISO week number. For this function to calculate correctly, the database DATEFIRST option must be set to 1 before the function is called.

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a UserDefinedFunction object variable by supplying the parent database and the name arguments in the constructor.
Dim udf As UserDefinedFunction
udf = New UserDefinedFunction(db, "IsOWeek")
'Set the TextMode property to false and then set the other properties.
udf.TextMode = False
udf.DataType = DataType.Int
udf.ExecutionContext = ExecutionContext.Caller
udf.FunctionType = UserDefinedFunctionType.Scalar
udf.ImplementationType = ImplementationType.TransactSql
'Add a parameter.
Dim par As UserDefinedFunctionParameter
par = New UserDefinedFunctionParameter(udf, "@DATE", DataType.DateTime)
udf.Parameters.Add(par)
'Set the TextBody property to define the user defined function.
udf.TextBody = "BEGIN  DECLARE @ISOweek int SET @ISOweek= DATEPART(wk,@DATE)+1 -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104') IF (@ISOweek=0) SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1 IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28)) SET @ISOweek=1 RETURN(@ISOweek) END;"
'Create the user defined function on the instance of SQL Server.
udf.Create()
'Remove the user defined function.
udf.Drop()


This code example shows how to create and remove a scalar user-defined function that has an input DateTime object parameter and an integer return type in Visual C#. The user-defined function is created on the AdventureWorks database. The example creates the user-defined function. ISOweek. This function takes a date argument and calculates the ISO week number. For this function to calculate correctly, the database DATEFIRST option must be set to 1 before the function is called.

//Connect to the local, default instance of SQL Server. 
{ 
Server srv = default(Server); 
srv = new Server(); 
//Reference the AdventureWorks database. 
Database db = default(Database); 
db = srv.Databases("AdventureWorks"); 
//Define a UserDefinedFunction object variable by supplying the parent database and the name arguments in the constructor. 
UserDefinedFunction udf = default(UserDefinedFunction); 
udf = new UserDefinedFunction(db, "IsOWeek"); 
//Set the TextMode property to false and then set the other properties. 
udf.TextMode = false; 
udf.DataType = DataType.Int; 
udf.ExecutionContext = ExecutionContext.Caller; 
udf.FunctionType = UserDefinedFunctionType.Scalar; 
udf.ImplementationType = ImplementationType.TransactSql; 
//Add a parameter. 
UserDefinedFunctionParameter par = default(UserDefinedFunctionParameter); 
par = new UserDefinedFunctionParameter(udf, "@DATE", DataType.DateTime); 
udf.Parameters.Add(par); 
//Set the TextBody property to define the user-defined function. 
udf.TextBody = "BEGIN DECLARE @ISOweek int SET @ISOweek= DATEPART(wk,@DATE)+1 -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104') IF (@ISOweek=0) SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1 IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28)) SET @ISOweek=1 RETURN(@ISOweek) END;"; 
//Create the user-defined function on the instance of SQL Server. 
udf.Create(); 
//Remove the user-defined function. 
udf.Drop(); 
} 

Community Additions

ADD
Show:
© 2014 Microsoft