Creazione, modifica e rimozione delle funzioni definite dall'utente
L'oggetto UserDefinedFunction fornisce funzionalità che consentono agli utenti di gestire a livello di codice le funzioni definite dall'utente in Microsoft SQL Server. Le funzioni definite dall'utente supportano parametri di input e di output e riferimenti diretti alle colonne delle tabelle.
In SQL Server è necessario registrare gli assembly all'interno di un database prima di poterli utilizzare in stored procedure, funzioni definite dall'utente, trigger e tipi di dati definiti dall'utente. In SMO questa funzionalità è supportata con l'oggetto SqlAssembly.
L'oggetto UserDefinedFunction fa riferimento all'assembly .NET con le proprietà AssemblyName, ClassName e MethodName.
Quando l'oggetto UserDefinedFunction fa riferimento a un assembly .NET, è necessario registrare l'assembly creando un oggetto SqlAssembly e aggiungendolo all'oggetto SqlAssemblyCollection che appartiene all'oggetto Database.
Esempio
Per utilizzare qualsiasi esempio di codice fornito, è necessario scegliere l'ambiente di programmazione, il modello di programmazione e il linguaggio di programmazione per la creazione dell'applicazione. Per ulteriori informazioni, vedere Creare un progetto SMO per Visual Basic in Visual Studio .NET o Creare un progetto SMO per Visual C# in Visual Studio .NET.
Creazione di una funzione scalare definita dall'utente in Visual Basic
In questo esempio di codice viene illustrato come creare e rimuovere una funzione scalare definita dall'utente con un parametro dell'oggetto DateTime di input e un tipo restituito integer in Visual Basic. La funzione definita dall'utente viene creata nel database AdventureWorks2012. Nell'esempio viene creata una funzione definita dall'utente, ISOweek, che accetta un argomento data per calcolare il numero di settimana ISO. Affinché il calcolo venga eseguito correttamente, è necessario impostare l'opzione DATEFIRST del database su 1 prima di chiamare la funzione.
'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()
Creazione di una funzione scalare definita dall'utente in Visual C#
In questo esempio di codice viene illustrato come creare e rimuovere una funzione scalare definita dall'utente con un parametro dell'oggetto DateTime di input e di un tipo restituito integer in Visual C#. La funzione definita dall'utente viene creata nel database AdventureWorks2012. Nell'esempio viene creata la funzione definita dall'utente. ISOweek. Questa funzione prende in considerazione un argomento di data e calcola il numero della settimana ISO. Affinché il calcolo venga eseguito correttamente, è necessario impostare l'opzione DATEFIRST del database su 1 prima di chiamare la funzione.
{
//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();
}
Creazione di una funzione scalare definita dall'utente in PowerShell
In questo esempio di codice viene illustrato come creare e rimuovere una funzione scalare definita dall'utente con un parametro dell'oggetto DateTime di input e un tipo restituito integer in Visual C#. La funzione definita dall'utente viene creata nel database AdventureWorks2012. Nell'esempio viene creata la funzione definita dall'utente. ISOweek. Questa funzione prende in considerazione un argomento di data e calcola il numero della settimana ISO. Affinché il calcolo venga eseguito correttamente, è necessario impostare l'opzione DATEFIRST del database su 1 prima di chiamare la funzione.
# 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()