建立、改變和移除使用者定義函數

適用範圍: 是SQL Server (所有支援的版本) 是Azure SQL Database 是Azure SQL 受控執行個體 是Azure Synapse Analytics

UserDefinedFunction物件提供的功能可讓使用者以程式設計方式管理中的使用者定義函數 Microsoft SQL Server 。 使用者定義函數支援輸入和輸出參數,也支援資料表資料行的直接參考。

SQL Server 需要先在資料庫內註冊組件,然後才能在預存程序、使用者定義函數、觸發程序和使用者定義資料類型中使用組件。 SMO 以 SqlAssembly 物件支援此功能。

UserDefinedFunction 物件會以 AssemblyNameClassNameMethodName 屬性參考 .NET 組件。

UserDefinedFunction 物件參考 .NET 組件時,您必須藉由建立 SqlAssembly 物件並將其加入至 SqlAssemblyCollection 物件 (後者屬於 Database 物件) 來註冊該組件。

範例

如果要使用所提供的任何程式碼範例,您必須選擇建立應用程式用的程式設計環境、程式設計範本,及程式設計語言。 如需詳細資訊,請參閱Visual Studio .net 中的建立 Visual C# SMO Project

在 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 中建立純量使用者定義函數

這個程式碼範例示範如何在 Visual C# 中,建立及移除具有輸入 DateTime 物件參數和整數傳回類型的純量使用者定義函數。 使用者定義函數會建立在 AdventureWorks2012 資料庫上。 本範例會建立使用者定義函數。 ISOweek. 這個函數採用日期引數並計算 ISO 週數。 為了讓這個函數能夠正確計算,必須先將資料庫 DATEFIRST 選項設定為 1 ,才能呼叫該函數。

# 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