post icon

Crear e Invocar funciones (UDF) en SQL Server

Las siglas UDF viene del inglés User Defined Functions (Funciones Definidas por el Usuario), esto quiere decir que podemos crear funciones personalizadas para hacer un determinado proceso que utilizaremos en más de una ocasión, luego tan sólo deberíamos invocarlo para volver a utilizarlo. En Transact-SQL existen 3 tipos de funciones:

  1. Funciones Escalares
  2. Funciones en línea
  3. Funciones en línea de múltiples sentencias

Para ver en acción esto, crearemos una base de datos (versión 2008 por el tipo de dato date que no es soportada en versiones anteriores) con una tabla y datos de ejemplo

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

CREATE DATABASE  [Devtroce];
GO
USE [Devtroce]
GO
/****** Object:  Table [dbo].[Persona]    Script Date: 01/18/2010 13:47:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Persona](
	[Codigo] [int] IDENTITY(1,1) NOT NULL,
	[Nombre] [varchar](50) NOT NULL,
	[Apellido] [varchar](50) NOT NULL,
	[DocumentoNro] [varchar](50) NOT NULL,
	[Direccion] [varchar](50) NULL,
	[Telefono] [varchar](50) NULL,
	[Email] [varchar](50) NULL,
	[FechaNacimiento] [date] NOT NULL,
 CONSTRAINT [PK_Persona] PRIMARY KEY CLUSTERED
(
	[Codigo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Persona] ON
INSERT [dbo].[Persona] ([Codigo], [Nombre], [Apellido], [DocumentoNro], [Direccion], [Telefono], [Email], [FechaNacimiento]) VALUES (1, N'Javier', N'Sosa', N'53409', N'Azara 343', NULL, NULL, CAST(0x560A0B00 AS Date))
INSERT [dbo].[Persona] ([Codigo], [Nombre], [Apellido], [DocumentoNro], [Direccion], [Telefono], [Email], [FechaNacimiento]) VALUES (3, N'Mariela', N'Guzman', N'9482309', NULL, NULL, NULL, CAST(0xEDFF0A00 AS Date))
INSERT [dbo].[Persona] ([Codigo], [Nombre], [Apellido], [DocumentoNro], [Direccion], [Telefono], [Email], [FechaNacimiento]) VALUES (4, N'Mirella', N'Gutierrez', N'9765610', N'Brasilia 399', N'996914', N'ejemplo@devtroce.com', CAST(0x11100B00 AS Date))
SET IDENTITY_INSERT [dbo].[Persona] OFF

Funciones Escalares

Las funciones escalares devuelven un valor escalar, eso quiere decir, que devuelve un valor único (una columna y una fila de datos)

Sintaxis:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

CREATE FUNCTION
(
	-- Agregar parametros de entrada
	<@Param1, sysname, @p1>
)
RETURNS
AS
BEGIN
	-- Declarar variables de retorno
	DECLARE <@ResultVar, sysname, @Result>
 
	-- Agregar el query SQL necesario
	SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>
 
	-- Retorna el valor de la función
	RETURN <@ResultVar, sysname, @Result>
END
GO

Ejemplo:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

CREATE FUNCTION fxEdad
(
	@Codigo int
)
RETURNS int
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Edad int
 
	-- Restar las fechas para ebtener las edad
	SELECT
		@Edad = datediff(year, P.FechaNacimiento, getdate())
	FROM
		Persona P
	WHERE
		P.Codigo = @Codigo;
 
	-- Retornar el valor escalar
	RETURN @Edad
 
END
GO

Implementación:

1
2
3
4

select
      P.Codigo, P.Nombre, P.Apellido, P.FechaNacimiento, dbo.fxEdad(P.Codigo)
from
     Persona P

Si observan bien, para invocar a las funciones debe especificarse su propietario, de lo contrario no funcionaría. Otro punto a tener muy en cuenta, es que la función se ejecuta por cada registro devuelto, lo cual puede afectar seriamente el rendimiento de nuestro query. Debe ser bien considerado su implementación, si nos ayudará a ganar velocidad de desarrollo compensa el rendimiento perdido. También debes recordar que aquí no puedes agregar sentencias INSERT ni UPDATE.

Funciones de Linea

Éste tipo de función retorna a diferencia del anterior, un conjunto de datos (filas y columnas)

Sintaxis:

1
2
3
4
5
6
7
8
9
10
11

CREATE FUNCTION 
(
-- Lista de parámetros
<@param1, sysname, @p1> ,...
)
RETURNS TABLE
AS
RETURN
(
-- Sentencia Transact SQL
)

Ejemplo:

1
2
3
4
5
6
7
8
9
10
11

CREATE FUNCTION fxLineaPersona
(
	@Codigo int
)
RETURNS TABLE
AS
RETURN
(
	SELECT *, dbo.fxEdad(@Codigo) AS Edad from Persona where Codigo = @Codigo
)
GO

Implementación:

1

SELECT * from dbo.fxLineaPersona(1);

El resultado que devuelve esta función es una tabla, por lo tanto podemos usarlo para mezclarlo en un JOIN de algún query, con la única diferencia que a ésta función no podemos aplicarle la clausula ORDER BY

Funciones en línea de múltiples sentencias

Éste tipo de función es muy similar al anterior, con la diferencia que está enfocado a implementar lógicas más fuertes, que requieran varias consultas SELECT para armar el resultado que deseamos que devuelva.

El ejemplo que estamos siguiendo queda corto hacerlo con una sola tabla, así que agregaré un select “tonto” que representará la idea de varios query’s.

Sintaxis:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CREATE FUNCTION <Table_Function_Name, sysname, FunctionName> 
(
-- Lista de parámetros
<@param1, sysname, @p1> <data_type_for_param1, , int>, ...
)
RETURNS 
-- variable de tipo tabla y su estructura
<@Table_Variable_Name, sysname, @Table_Var> TABLE 
(
<Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
<Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
	-- Sentencias que cargan de datos la tabla declarada
	RETURN 
END

Ejemplo:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

ALTER FUNCTION fx_multiPersona
(
	 @Codigo int
	,@Edad int
)
RETURNS @credito TABLE
(
	 Codigo int
	,NombreApellido varchar(50)
	,Edad int
)
AS
BEGIN
	INSERT INTO @credito
		SELECT Codigo, Nombre + Apellido, dbo.fxEdad(Codigo)
			FROM Persona WHERE Codigo = @Codigo;
 
	INSERT INTO @credito
		SELECT Codigo, Nombre + Apellido, dbo.fxEdad(Codigo)
			FROM Persona WHERE dbo.fxEdad(Codigo) > @Edad
	RETURN 
END
GO

Implementación:

1

SELECT * from dbo.fx_multiPersona(1, 30)

Articulos Relacionados:

18 enero 2010

6 Comentarios

Deja tu Comentario
  1. Carlos Rojas CHILE Mozilla Firefox Windows
    14 febrero 2011 at 17:00 #

    Hola mira he creado una funcion en sql 2005 para obtener el calculo de zona horaria, mi problema es que necesito colocarla como dato por defecto en las tablas que tienen campos de tipo de dato datetime.

    como puedo hacerlo?
    para no entrar a todo la aplicacion y buscar todas las instancias en donde realizo insert

    de antemano muchas gracias

    Responder
  2. monkeyhand MEXICO Internet Explorer Windows
    13 diciembre 2010 at 17:49 #

    Muchas gracias por la respuesta tan pronta, el ejercicio que me mandaste hace referencia hacia una tabla en especifico, por lo que veo es que tambien le puedes modificar o renovar la consulta, ¿posiblemente puedo enviar mi funcion como consulta?

    Ejemplo:

    CREATE FUNCTION [dbo].[EQUIPO-USER](@parametro varchar(18) )
    RETURNS TABLE
    AS
    RETURN (SELECT dbo.TBDISCO.ESPACIO, dbo.TBEQUIPO.EQUIPO,
    dbo.TBEQUIPO.SISTEMA, dbo.TBEQUIPO.USUARIO, dbo.TBEQUIPO.RAM,
    dbo.TBNETWORK.IP, dbo.TBNETWORK.GRUPO, dbo.TBPROCESADOR.SO
    FROM dbo.TBDISCO
    INNER JOIN dbo.TBEQUIPO ON dbo.TBDISCO.EQUIPO = dbo.TBEQUIPO.EQUIPO
    INNER JOIN dbo.TBNETWORK ON dbo.TBEQUIPO.EQUIPO = dbo.TBNETWORK.EQUIPO
    INNER JOIN dbo.TBPROCESADOR ON dbo.TBEQUIPO.EQUIPO = dbo.TBPROCESADOR.EQUIPO
    WHERE dbo.TBEQUIPO.EQUIPO=@parametro)

    y en sql la mando llamar:

    SELECT * FROM dbo.[EQUIPO-USER](‘SOPORTE-TEC’)

    esta misma instruccion la quiero en el crystal, como le puedo hacer , espero que me haya explicado

    Responder
    • GeekZero PARAGUAY Google Chrome Windows
      13 diciembre 2010 at 22:02 #

      Porque quieres usar una función? imagino que vienes de usar postgres por eso la confusión, los udf en t-sql están optimizados para utilizarse como columnas de querys o conjunto de datos para unir en joins o wheres dentro de otros querys.

      Lo mas optimo para lo que comentas es usar un procedimiento almacenado o stored procedure, asi:

      1
2
3
4
5
6
7
8
9
10

      CREATE PROCEDURE EQUIPOUSER @parametro varchar(18) 
AS
SELECT dbo.TBDISCO.ESPACIO, dbo.TBEQUIPO.EQUIPO,
dbo.TBEQUIPO.SISTEMA, dbo.TBEQUIPO.USUARIO, dbo.TBEQUIPO.RAM,
dbo.TBNETWORK.IP, dbo.TBNETWORK.GRUPO, dbo.TBPROCESADOR.SO
FROM dbo.TBDISCO
INNER JOIN dbo.TBEQUIPO ON dbo.TBDISCO.EQUIPO = dbo.TBEQUIPO.EQUIPO
INNER JOIN dbo.TBNETWORK ON dbo.TBEQUIPO.EQUIPO = dbo.TBNETWORK.EQUIPO
INNER JOIN dbo.TBPROCESADOR ON dbo.TBEQUIPO.EQUIPO = dbo.TBPROCESADOR.EQUIPO
WHERE dbo.TBEQUIPO.EQUIPO=@parametro

      Invocando lo asi:

      1
2

      EXEC EQUIPOUSER
EXECUTE EQUIPOUSER

      Desde Dotnet le cambiarias el sqlcommand algo asi:

      1
2

      cmd.commandtype = storedprocedure;
cmd.commandtext = "EQUIPOUSER";
      Responder
  3. monkeyhand MEXICO Internet Explorer Windows
    13 diciembre 2010 at 15:50 #

    excelente tutortial para la gente novata como yo, ahora estas funciones como las puedo visualizar en un reporte de crystal reports. de antemano muchas gracias

    Responder

Responder