创建、更改和删除用户定义函数Creating, Altering, and Removing User-Defined Functions

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics

UserDefinedFunction对象提供允许用户以编程方式管理中用户定义的函数的功能 MicrosoftMicrosoft SQL ServerSQL ServerThe UserDefinedFunction object provides functionality that lets users programmatically manage user-defined functions in MicrosoftMicrosoft SQL ServerSQL Server. 用户定义函数支持输入和输出参数,还支持对表列的直接引用。User-defined functions support input and output parameters, and also support direct references to table columns.

SQL ServerSQL 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 使用 SqlAssembly 对象支持此项功能。SMO supports this feature with the SqlAssembly object.

UserDefinedFunction 对象使用 AssemblyNameClassNameMethodName 属性来引用 .NET 程序集。The UserDefinedFunction object references the .NET assembly with the AssemblyName, ClassName, and MethodName properties.

UserDefinedFunction 对象引用 .NET 程序集时,您必须通过创建 SqlAssembly 对象并将其添加到 SqlAssemblyCollection 对象(属于 Database 对象)来注册该程序集。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.

示例Example

若要使用所提供的任何代码示例,您必须选择创建应用程序所需的编程环境、编程模板和编程语言。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. 有关详细信息,请参阅 在 Visual Studio .net 中创建 Visual C# SMO 项目For more information, see Create a Visual C# SMO Project in Visual Studio .NET.

在 Visual Basic 中创建标量用户定义函数Creating a Scalar User-Defined Function in Visual Basic

此代码示例说明如何在 Visual BasicVisual Basic 中创建和删除具有 DateTime 输入对象参数和整数返回类型的标量用户定义函数。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 BasicVisual Basic. 此用户定义函数是对 AdventureWorks2012AdventureWorks2012 数据库创建的。The user-defined function is created on the AdventureWorks2012AdventureWorks2012 database. 该示例创建了用户定义函数 ISOweek,此函数带有一个日期参数,用于计算 ISO 周号。The example creates a user-defined function, ISOweek, which takes a date argument and calculates the ISO week number. 要使此函数能正确计算,必须在调用此函数之前将数据库 DATEFIRST 选项设置为 1。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 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# 中创建标量用户定义函数Creating a Scalar User-Defined Function in Visual C#

此代码示例说明如何在 Visual C#Visual C# 中创建和删除具有 DateTime 输入对象参数和整数返回类型的标量用户定义函数。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#Visual C#. 此用户定义函数是对 AdventureWorks2012AdventureWorks2012 数据库创建的。The user-defined function is created on the AdventureWorks2012AdventureWorks2012 database. 该示例将创建用户定义函数。The example creates the user-defined function. ISOweek.ISOweek. 此函数使用日期参数来计算 ISO 周数。This function takes a date argument and calculates the ISO week number. 要使此函数能正确计算,必须在调用此函数之前将数据库 DATEFIRST 选项设置为 1For 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 = 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 中创建标量用户定义函数Creating a Scalar User-Defined Function in PowerShell

此代码示例说明如何在 Visual C#Visual C# 中创建和删除具有 DateTime 输入对象参数和整数返回类型的标量用户定义函数。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#Visual C#. 此用户定义函数是对 AdventureWorks2012AdventureWorks2012 数据库创建的。The user-defined function is created on the AdventureWorks2012AdventureWorks2012 database. 该示例将创建用户定义函数。The example creates the user-defined function. ISOweek.ISOweek. 此函数使用日期参数来计算 ISO 周数。This function takes a date argument and calculates the ISO week number. 要使此函数能正确计算,必须在调用此函数之前将数据库 DATEFIRST 选项设置为 1For this function to calculate correctly, the database DATEFIRST option must be set to 1 before the function is called.

# 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()  

另请参阅See Also

UserDefinedFunction