Ingeniería Systems: Funciones definidas por el usuario en SQL Server

Create Function With SchemaBinding

Crea funciones con dependencias, para poder proteger la estructura de las tablas usadas en una función. Es decir usando with schemabinding, no se podrá usar Alter ni Drop en las tablas de las que depende.

Create Function Emp_Dept (@Ndept nvarchar(20))
Returns Table
With SchemaBinding
As
Return(Select Emp_No, Apellido, Salario, DNombre From Dbo.Emp
Inner Join Dbo.Dept
On Dept.Dept_no = Emp.Dept_no
where DNombre = @NDept)

Como vemos en el ejemplo al crear las tablas con dependencias, hemos de indicar cuál es su propietario.

Si intentamos borrar o modificar la tabla nos mostrará un error.

Drop Table Emp

Servidor: mensaje 3729, nivel 16, estado 1, línea 1

No se puede DROP TABLE 'Emp' porque el objeto 'Emp_Dept' le hace referencia.

Funciones No deterministas:

Trabajan siempre con el mismo tipo de valor pero devuelven cada vez un valor diferente.

Ej. getdate() trabaja siempre con la fecha del sistema pero devuelve un valor diferente, @@Error, siempre trabaja con los errores del sistema pero devuelve errores diferentes.

A la hora de crear funciones definidas por el usuario, no podemos usar funciones no deterministas.

Ejercicios:

1. Crear una función que pasándole una fecha y un separador a elegir nos debe cambiar el separador de la fecha que le estamos pasando por el separador elegido, ejemplo pasándole la fecha 01/12/2001 y el separador “:” obtendremos 01:12:2001. hemos de tener en cuenta que la fecha que le pasemos no tiene porque tener el separador /.

Para probar que la función es correcta, seleccionaremos todas las fechas de alta de la tabla Empleados y le pasaremos el separador “:”

Create Function CambiaFecha(@fecha datetime, @separador nvarchar(1))
Returns nvarchar(10)
Begin
Return
(Convert(nvarchar(6),(Day(@fecha)))
+ @separador
+ Convert(nvarchar(6),(Month(@fecha)))
+ @separador
+ Convert(nvarchar(6),(Year(@fecha))))
End

Al declarar la función usamos convert para convertir el día, mes o año a caracter, ya que si intentamos concatenar números con carácteres da error porque piensa que intentamos sumar.

Select Dbo.CambiaFecha(fecha_alt, '*') as Resultado from Emp

2. Crear una función que pasándole el parámetro ‘Completo’ Seleccione el apellido junto su número de empleado en una columna y en otra el departamento y pasándole ‘Apellido’ Seleccione el apellido y el departamento.

create function Empleados(@Param nvarchar(50))
Returns @Tabla table
(Empleado nvarchar(50)
,Dept_no int)
as
Begin
if (@Param = 'Completo')
insert into @Tabla
Select Apellido + ' ' + Convert(char(6),emp_no)
, dept_no from emp
else
insert into @Tabla
Select Apellido, Dept_no
from emp
Return
end

Select * from dbo.Empleados('Completo')

Select * from dbo.Empleados('Otro')

3. Crear una función que dependiendo de los datos que le enviemos, nos devolverá un informe sobre los empleados. Los parámetros que le podemos enviar a la función son: Nº Departamento, Nº Empleado, Fecha u Oficio. Dependiendo del dato, mostraremos unos datos u otros.

create function Empleados_rpt(@Param nvarchar(50))

Returns @Tabla table
(Empleado nvarchar(200))
as
Begin
if (isnumeric(@Param) = 1)
insert into @Tabla
select isnull('El señor ' + LTRIM(cast(apellido as nvarchar(15)))
+ ' con cargo de '
+ ltrim(cast(oficio as nvarchar(15)))
+ ' se dió de alta el '
+ cast(day(fecha_alt) as char(2)) + ' de '
+ ltrim(cast(datename(month,fecha_alt) as nvarchar(15)))
+ ' de '
+ cast(year(fecha_alt) as char(4))
,'EMPLEADO SIN NOMBRE')
as [DATOS EMPLEADOS]
from emp
where emp_no = @Param
else
begin
if (isdate(@Param) = 1)
BEGIN
insert into @Tabla
select isnull('El señor ' + LTRIM(cast(apellido as nvarchar(15)))
+ ' con cargo de '
+ ltrim(cast(oficio as nvarchar(15)))
+ ' se dió de alta el '
+ cast(day(fecha_alt) as char(2)) + ' de '
+ ltrim(cast(datename(month,fecha_alt) as nvarchar(15)))
+ ' de '
+ cast(year(fecha_alt) as char(4))
,'EMPLEADO SIN NOMBRE')
as [DATOS EMPLEADOS]
from emp
where fecha_alt > @Param
END
ELSE
BEGIN
insert into @Tabla
select isnull('El señor ' + LTRIM(cast(apellido as nvarchar(15)))
+ ' con cargo de '
+ ltrim(cast(oficio as nvarchar(15)))
+ ' se dió de alta el '
+ cast(day(fecha_alt) as char(2)) + ' de '
+ ltrim(cast(datename(month,fecha_alt) as nvarchar(15)))
+ ' de '
+ cast(year(fecha_alt) as char(4))
,'EMPLEADO SIN NOMBRE')
as [DATOS EMPLEADOS]
from emp
where apellido like @Param
END
end
Return
end