Tutorial de SQL Server

Hemos visto el primer tipo de funciones definidas por el usuario, que retornan un valor escalar. Ahora veremos las funciones con varias instrucciones que retornan una tabla.

Las funciones que retornan una tabla pueden emplearse en lugar de un "from" de una consulta.

Este tipo de función es similar a un procedimiento almacenado; la diferencia es que la tabla retornada por la función puede ser referenciada en el "from" de una consulta, pero el resultado de un procedimiento almacenado no.

También es similar a una vista; pero en las vistas solamente podemos emplear "select", mientras que en funciones definidas por el usuario podemos incluir sentencias como "if", llamadas a funciones, procedimientos, etc.

Sintaxis:

 create function NOMBREFUNCION
 (@PARAMETRO TIPO)
 returns @NOMBRETABLARETORNO table-- nombre de la tabla
 --formato de la tabla
 (CAMPO1 TIPO,
  CAMPO2 TIPO,
  CAMPO3 TIPO
 )
 as
 begin
   insert @NOMBRETABLARETORNO
    select CAMPOS
     from TABLA
     where campo OPERADOR @PARAMETRO
   RETURN
 end

Como cualquier otra función, se crea con "create function" seguida del nombre de la función; luego (opcionalmente) los parámetros de entrada con su tipo de dato.

La cláusula "returns" define un nombre de variable local para la tabla que retornará, el tipo de datos a retornar (que es "table") y el formato de la misma (campos y tipos).

El cuerpo de la función se define también en un bloque "begin... end", el cual contiene las instrucciones que insertan filas en la variable (tabla que será retornada) definida en "returns". "return" indica que las filas insertadas en la variable son retornadas; no puede ser un argumento.

El siguiente ejemplo crea una función denominada "f_ofertas" que recibe un parámetro. La función retorna una tabla con el codigo, título, autor y precio de todos los libros cuyo precio sea inferior al parámetro:

 create function f_ofertas
 (@minimo decimal(6,2))
 returns @ofertas table-- nombre de la tabla
 --formato de la tabla
 (codigo int,
  titulo varchar(40),
  autor varchar(30),
  precio decimal(6,2)
 )
 as
 begin
   insert @ofertas
    select codigo,titulo,autor,precio
    from libros
    where precio < @minimo
   return
 end;

Las funciones que retornan una tabla pueden llamarse sin especificar propietario:

 select *from f_ofertas(30);
 select *from dbo.f_ofertas(30);

Dijimos que este tipo de función puede ser referenciada en el "from" de una consulta; la siguiente consulta realiza un join entre la tabla "libros" y la tabla retornada por la función "f_ofertas":

 select *from libros as l
  join dbo.f_ofertas(25) as o
  on l.codigo=o.codigo;

Se puede llamar a la función como si fuese una tabla o vista listando algunos campos:

 select titulo,precio from dbo.f_ofertas(40);

136 - Funciones de tabla de varias instrucciones

Retornar