Crear, modificar y eliminar las funciones definidas por el usuario
El objeto UserDefinedFunction proporciona la funcionalidad que permite a los usuarios administrar mediante programación las funciones definidas por el usuario en MicrosoftSQL Server. Las funciones definidas por el usuario admiten los parámetros de entrada y salida y también admiten las referencias directas a las columnas de la tabla.
SQL Server requiere registrar los ensamblados en una base de datos antes de que se puedan utilizar en los procedimientos almacenados, funciones definidas por el usuario, desencadenadores y los tipos de datos definidos por el usuario. SMO admite esta característica con el objeto SqlAssembly.
El objeto UserDefinedFunction hace referencia al ensamblado .NET con las propiedades AssemblyName, MethodName y ClassName.
Cuando el objeto UserDefinedFunction hace referencia a un ensamblado .NET, debe registrar el ensamblado creando un objeto SqlAssembly y agregándolo al objeto SqlAssemblyCollection, que pertenece al objeto Database.
Ejemplo
Para utilizar cualquier ejemplo de código que se proporcione, deberá elegir el entorno de programación, la plantilla de programación y el lenguaje de programación en los que crear su aplicación. Para obtener más información, vea Cómo crear un proyecto de Visual Basic SMO en Visual Studio .NET o Cómo crear un proyecto de Visual C# SMO en Visual Studio .NET.
Crear una función escalar definida por el usuario en Visual Basic
En este ejemplo de código se muestra cómo crear y quitar una función escalar definida por el usuario que tiene un parámetro de objeto DateTime de entrada y un tipo de valor devuelto entero de Visual Basic. La función definida por el usuario se crea en la base de datos de AdventureWorks. En el ejemplo se crea una función definida por el usuario, ISOweek, que toma un argumento de fecha y calcula el número de semana ISO. Para que esta función realice los cálculos correctamente, la opción DATEFIRST de base de datos debe establecerse en 1 antes de que se llame a la función.
'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()
Crear una función escalar definida por el usuario en Visual C#
En este ejemplo de código se muestra cómo crear y quitar una función escalar definida por el usuario que tiene un parámetro de objeto DateTime de entrada y un tipo de valor devuelto entero de Visual C#. La función definida por el usuario se crea en la base de datos de AdventureWorks. En el ejemplo se crea la función definida por el usuario. ISOweek. Esta función usa un argumento de fecha para calcular el número de semana ISO. Para que esta función realice los cálculos correctamente, la opción DATEFIRST de base de datos debe establecerse en 1 antes de que se llame a la función.
//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();
}