创建、更改和删除用户定义函数

项目
06/18/2013

UserDefinedFunction 对象提供了一项功能，用户可以使用此功能以编程方式管理 Microsoft SQL Server 中的用户定义函数。用户定义函数支持输入和输出参数，还支持对表列的直接引用。

SQL Server 要求先在数据库中注册程序集，然后才能在存储过程、用户定义函数、触发器和用户定义数据类型中使用这些程序集。 SMO 使用 SqlAssembly 对象支持此项功能。

UserDefinedFunction 对象使用 AssemblyName、ClassName 和 MethodName 属性来引用 .NET 程序集。

当 UserDefinedFunction 对象引用 .NET 程序集时，您必须通过创建 SqlAssembly 对象并将其添加到 SqlAssemblyCollection 对象（属于 Database 对象）来注册该程序集。

示例

若要使用所提供的任何代码示例，您必须选择创建应用程序所需的编程环境、编程模板和编程语言。有关详细信息，请参阅在 Visual Studio .NET 中创建 Visual Basic SMO 项目或在 Visual Studio .NET 中创建 Visual C# SMO 项目。

在 Visual Basic 中创建标量用户定义函数

此代码示例说明如何在 Visual Basic 中创建和删除具有 DateTime 输入对象参数和整数返回类型的标量用户定义函数。此用户定义函数是针对 AdventureWorks2012 数据库创建的。该示例创建了用户定义函数 ISOweek，此函数带有一个日期参数，用于计算 ISO 周号。要使此函数能正确计算，必须在调用此函数之前将数据库 DATEFIRST 选项设置为 1。

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks2012 2008R2 database.
Dim db As Database
db = srv.Databases("AdventureWorks2012")
'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()

在 Visual C# 中创建标量用户定义函数

此代码示例说明如何在 Visual C# 中创建和删除具有 DateTime 输入对象参数和整数返回类型的标量用户定义函数。此用户定义函数是对 AdventureWorks2012 数据库创建的。该示例将创建用户定义函数 ISOweek。此函数接受一个日期参数并计算 ISO 周数。要使此函数能正确计算，必须在调用此函数之前将数据库 DATEFIRST 选项设置为 1。

{
            //Connect to the local, default instance of SQL Server. 
           Server srv = new Server();
            //Reference the AdventureWorks2012 database. 
           Database db = srv.Databases["AdventureWorks2012"];

            //Define a UserDefinedFunction object variable by supplying the parent database and the name arguments in the constructor. 
            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 = 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();
        }

在 PowerShell 中创建标量用户定义函数

# Set the path context to the local, default instance of SQL Server and get a reference to AdventureWorks2012
CD \sql\localhost\default\databases
$db = get-item Adventureworks2012

# Define a user defined function object variable by supplying the parent database and name arguments in the constructor. 
$udf  = New-Object -TypeName Microsoft.SqlServer.Management.SMO.UserDefinedFunction `
-argumentlist $db, "IsOWeek"

# Set the TextMode property to false and then set the other properties. 
$udf.TextMode = $false
$udf.DataType = [Microsoft.SqlServer.Management.SMO.DataType]::Int 
$udf.ExecutionContext = [Microsoft.SqlServer.Management.SMO.ExecutionContext]::Caller
$udf.FunctionType = [Microsoft.SqlServer.Management.SMO.UserDefinedFunctionType]::Scalar
$udf.ImplementationType = [Microsoft.SqlServer.Management.SMO.ImplementationType]::TransactSql

# Define a Parameter object variable by supplying the parent function, name and type arguments in the constructor.
$type = [Microsoft.SqlServer.Management.SMO.DataType]::DateTime
$par  = New-Object -TypeName Microsoft.SqlServer.Management.SMO.UserDefinedFunctionParameter `
-argumentlist $udf, "@DATE",$type

# Add the parameter to the function
$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()

请参阅

参考

UserDefinedFunction

通过

创建、更改和删除用户定义函数

示例

在 Visual Basic 中创建标量用户定义函数

在 Visual C# 中创建标量用户定义函数

在 PowerShell 中创建标量用户定义函数

请参阅

参考

其他资源