Tipos de funciones
SQL Server 2005 admite funciones definidas por el usuario y funciones del sistema integradas.
Funciones escalares
Las funciones escalares definidas por el usuario devuelven un único valor de datos del tipo definido en la cláusula RETURNS. Las funciones escalares en línea no tienen cuerpo; el valor escalar es el resultado de una sola instrucción. Para una función escalar de múltiples instrucciones, el cuerpo de la función, definido en un bloque BEGIN...END, contiene una serie de instrucciones Transact-SQL que devuelven el valor único. El tipo devuelto puede ser de cualquier tipo de datos excepto text, ntext, image, cursor y timestamp.
En el ejemplo siguiente se crea una función escalar con múltiples instrucciones. La función toma un valor de entrada, ProductID, y devuelve un solo valor de datos, la cantidad agregada del producto especificado en el inventario.
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL
DROP FUNCTION ufnGetInventoryStock;
GO
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
RETURNS int
AS
-- Returns the stock level for the product.
BEGIN
DECLARE @ret int;
SELECT @ret = SUM(p.Quantity)
FROM Production.ProductInventory p
WHERE p.ProductID = @ProductID
AND p.LocationID = '6';
IF (@ret IS NULL)
SET @ret = 0;
RETURN @ret;
END;
GO
En el ejemplo siguiente se utiliza la función ufnGetInventoryStock para devolver la cantidad de inventario actual de productos que tienen un ProductModelID entre 75 y 80.
USE AdventureWorks;
GO
SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;
GO
Funciones con valores de tabla
Las funciones con valores de tabla definidas por el usuario devuelven un tipo de datos table. Las funciones con valores de tabla en línea no tienen cuerpo; la tabla es el conjunto de resultados de una sola instrucción SELECT.
En el ejemplo siguiente se crea una función con valores de tabla en línea. La función toma un parámetro de entrada, Id. de cliente (almacén), y devuelve las columnas ProductID, Name, y el agregado de las ventas del año hasta la fecha como YTD Total para cada producto vendido en el almacén.
USE AdventureWorks;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
WHERE SH.CustomerID = @storeid
GROUP BY P.ProductID, P.Name
);
GO
En el ejemplo siguiente se invoca la función y especifica el Id. de cliente 602.
SELECT * FROM Sales.ufn_SalesByStore (602);
Para una función con valores de tabla de múltiples instrucciones, el cuerpo de la función, definido en un bloque BEGIN...END, contiene una serie de instrucciones Transact-SQL que generan e insertan filas en la tabla que se va a devolver.
En el ejemplo siguiente se crea una función con valores de tabla. La función toma un solo parámetro de entrada, EmployeeID, y devuelve una lista de todos los empleados que informan al empleado especificado directa o indirectamente. La función se invoca a continuación especificando el identificador de empleado 109.
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE
(
EmployeeID int primary key NOT NULL,
Name nvarchar(255) NOT NULL,
Title nvarchar(50) NOT NULL,
EmployeeLevel int NOT NULL,
Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
(SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
1,
CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE e.EmployeeID = @InEmpID
UNION ALL
SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
EmployeeLevel + 1,
CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +
LastName)
FROM HumanResources.Employee as e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
-- copy the required columns to the result of the function
INSERT @retFindReports
SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
FROM DirectReports
RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.ufn_FindReports(109)
ORDER BY Sort;
GO
Funciones integradas
SQL Server proporciona las funciones integradas para ayudarle a realizar diversas operaciones. No se pueden modificar. Puede utilizar funciones integradas en instrucciones Transact-SQL para:
- Tener acceso a información de las tablas del sistema de SQL Server sin tener acceso a las tablas del sistema directamente. Para obtener más información, vea Usar las funciones del sistema.
- Realizar tareas habituales como SUM, GETDATE o IDENTITY. Para obtener más información, vea Funciones (Transact-SQL).
Las funciones integradas devuelven tipos de datos escalares o table. Por ejemplo, @@ERROR devuelve 0 si la última instrucción Transact-SQL se ejecutó correctamente. Si la instrucción generó un error, @@ERROR devuelve el número de error. Y la función SUM(parameter) devuelve la suma de todos los valores del parámetro.
Vea también
Otros recursos
Descripción de funciones definidas por el usuario