ユーザー定義関数の作成、変更、および削除
UserDefinedFunction オブジェクトは、Microsoft SQL Server のユーザー定義関数をユーザーがプログラムによって管理できるようにする機能を提供します。 ユーザー定義関数では、入力パラメーターおよび出力パラメーターに加えて、テーブル列への直接参照もサポートされます。
SQL Server では、ストアド プロシージャ、ユーザー定義関数、トリガー、およびユーザー定義データ型内でアセンブリを使用できるようにするには、アセンブリがデータベース内に登録される必要があります。 SMO は、SqlAssembly オブジェクトを使用してこの機能をサポートします。
UserDefinedFunction オブジェクトは、AssemblyName プロパティ、ClassName プロパティ、および MethodName プロパティを使用して .NET アセンブリを参照します。
UserDefinedFunction オブジェクトが .NET アセンブリを参照する場合、SqlAssembly オブジェクトを作成し、それを Database オブジェクトに属する SqlAssemblyCollection オブジェクトに追加することによって、アセンブリを登録する必要があります。
例
提供されているコード例を使用するには、アプリケーションを作成するプログラミング環境、プログラミング テンプレート、およびプログラミング言語を選択する必要があります。 詳細については、「Visual Studio .NET での Visual Basic SMO プロジェクトの作成」または「Visual Studio .NET での Visual C# SMO プロジェクトの作成」を参照してください。
Visual Basic でのユーザー定義スカラー関数の作成
このコード例は、入力の DateTime オブジェクト パラメーターおよび整数型の戻り値を持つユーザー定義のスカラー関数を Visual Basic で作成および削除する方法を示しています。ユーザー定義関数は AdventureWorks2012 データベースに作成されます。 この例では、日付引数を取得して ISO 週番号を計算するユーザー定義関数 ISOweek が作成されます。 この関数で正しい計算を行うためには、関数を呼び出す前に、データベースの 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# でのユーザー定義スカラー関数の作成
このコード例は、入力の DateTime オブジェクト パラメーターおよび整数型の戻り値を使用するユーザー定義のスカラー関数を Visual C# で作成および削除する方法を示しています。 ユーザー定義関数は 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 でのユーザー定義スカラー関数の作成
このコード例は、入力の DateTime オブジェクト パラメーターおよび整数型の戻り値を持つユーザー定義のスカラー関数を Visual C# で作成および削除する方法を示しています。 ユーザー定義関数は 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()