Création, modification et suppression de fonctions définies par l'utilisateur
L'objet UserDefinedFunction fournit des fonctionnalités qui permettent aux utilisateurs de gérer par programme des fonctions définies par l'utilisateur dans MicrosoftSQL Server. Les fonctions définies par l'utilisateur prennent en charge les paramètres d'entrée et de sortie, ainsi que les références directes aux colonnes de table.
SQL Server requiert l'enregistrement d'assemblys dans une base de données pour que ceux-ci puissent être utilisés dans des procédures stockées, des fonctions définies par l'utilisateur, des déclencheurs et des types de données définis par l'utilisateur. SMO prend en charge cette fonctionnalité avec l'objet SqlAssembly.
L'objet UserDefinedFunction référence l'assembly .NET avec les propriétés AssemblyName, ClassName et MethodName.
Lorsque l'objet UserDefinedFunction référence un assembly .NET, vous devez enregistrer l'assembly en créant un objet SqlAssembly et en l'ajoutant à l'objet SqlAssemblyCollection, qui fait partie de l'objet Database.
Exemple
Pour utiliser un exemple de code fourni, vous devez sélectionner l'environnement, le modèle et le langage de programmation à utiliser pour créer votre application. Pour plus d'informations, consultez Procédure : créer un projet SMO Visual Basic dans Visual Studio .NET ou Procédure : créer un projet SMO Visual C# dans Visual Studio .NET.
Création d'une fonction scalaire définie par l'utilisateur en Visual Basic
Cet exemple de code montre comment créer et supprimer une fonction scalaire définie par l'utilisateur qui a un paramètre d'objet DateTime en entrée et un type de retour entier en Visual Basic. La fonction définie par l'utilisateur est créée dans la base de données AdventureWorks. L'exemple crée une fonction définie par l'utilisateur, ISOweek, qui accepte un argument de date et calcule le numéro de semaine ISO. Pour que ce calcul puisse être correctement réalisé, la valeur 1 doit être affectée à l'option de base de données DATEFIRST avant l'appel de la fonction.
'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'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()
Création d'une fonction scalaire définie par l'utilisateur en Visual C#
Cet exemple de code montre comment créer et supprimer une fonction scalaire définie par l'utilisateur qui a un paramètre d'objet DateTime en entrée et un type de retour entier en Visual C#. La fonction définie par l'utilisateur est créée dans la base de données AdventureWorks. L'exemple crée la fonction définie par l'utilisateur. ISOweek. Cette fonction contient un argument date et calcule le numéro de semaine ISO. Pour que ce calcul puisse être correctement réalisé, la valeur 1 doit être affectée à l'option de base de données DATEFIRST avant l'appel de la fonction.
//Connect to the local, default instance of SQL Server.
{
Server srv = default(Server);
srv = new Server();
//Reference the AdventureWorks database.
Database db = default(Database);
db = srv.Databases("AdventureWorks");
//Define a UserDefinedFunction object variable by supplying the parent database and the name arguments in the constructor.
UserDefinedFunction udf = default(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 = default(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();
}