CREACION DE FUNCIONES EN SQL SERVER

PROGRAMACIÓN CON TRANSACT: Creación de Funciones

En SQL Server, las funciones son bloques de código SQL que realizan una operación específica y devuelven un valor. Las funciones son similares a los procedimientos almacenados, pero están diseñadas principalmente para realizar cálculos y devolver resultados, y tienen ciertas limitaciones y ventajas únicas en comparación con los procedimientos almacenados.

Tipos de Funciones en SQL Server

Existen varios tipos de funciones en SQL Server, cada una con sus propias características y usos:

1. Funciones Escalares:
   • Devuelven un único valor de un tipo de datos específico.
   • Se pueden utilizar en instrucciones SQL como si fueran expresiones o columnas.
2. Funciones con Valores de Tabla:
   • Devuelven un conjunto de resultados en forma de tabla.
   • Pueden ser de dos tipos: funciones con valores de tabla en línea y funciones con valores de tabla con múltiples instrucciones.
3. Funciones del Sistema:
   • Funciones predefinidas proporcionadas por SQL Server para realizar operaciones comunes, como obtener la fecha actual, manipular cadenas de texto, y más.

Funciones Escalares

Estas funciones devuelven un único valor de un tipo de datos específico. Aquí se muestra cómo crear una función escalar:

Crear una Función Escalar

— Crear una función escalar que calcula el área de un círculo
CREATE FUNCTION dbo.CalculateCircleArea (
@Radius FLOAT
)
RETURNS FLOAT
AS
BEGIN
DECLARE @Area FLOAT;
SET @Area = PI() * @Radius * @Radius;
RETURN @Area;
END;

Usar una Función Escalar

— Usar la función escalar en una consulta

SELECT dbo.CalculateCircleArea(5) AS CircleArea;

Funciones con Valores de Tabla en Línea

Estas funciones devuelven una tabla y son similares a una vista parametrizada.

Crear una Función con Valores de Tabla en Línea

— Crear una función con valores de tabla en línea que devuelve los productos de una categoría específica

CREATE FUNCTION dbo.GetProductsByCategory (
@CategoryID INT
)
RETURNS TABLE
AS
RETURN (
SELECT ProductID, ProductName, Price
FROM Products
WHERE CategoryID = @CategoryID
);

Usar una Función con Valores de Tabla en Línea

— Usar la función con valores de tabla en una consulta

SELECT * FROM dbo.GetProductsByCategory(1);

Funciones con Valores de Tabla con Múltiples Instrucciones

Estas funciones permiten realizar múltiples operaciones antes de devolver la tabla.

Crear una Función con Valores de Tabla con Múltiples Instrucciones

— Crear una función con valores de tabla con múltiples instrucciones

CREATE FUNCTION dbo.GetTopProductsByCategory (
@CategoryID INT,
@TopN INT
)
RETURNS @TopProducts TABLE (
ProductID INT,
ProductName NVARCHAR(50),
Price DECIMAL(10, 2)
)
AS
BEGIN
INSERT INTO @TopProducts
SELECT TOP (@TopN) ProductID, ProductName, Price
FROM Products
WHERE CategoryID = @CategoryID
ORDER BY Price DESC;
RETURN;
END;

Usar una Función con Valores de Tabla con Múltiples Instrucciones

— Usar la función con valores de tabla con múltiples instrucciones en una consulta

SELECT * FROM dbo.GetTopProductsByCategory(1, 5);

Funciones del Sistema

SQL Server proporciona muchas funciones del sistema predefinidas para realizar operaciones comunes. Algunos ejemplos incluyen:

Ejemplos de Funciones del Sistema

• Fecha y Hora:
  • GETDATE(): Devuelve la fecha y hora actuales.
  • DATEADD(): Agrega un intervalo a una fecha.

— Obtener la fecha y hora actuales

SELECT GETDATE() AS CurrentDateTime;

— Agregar 7 días a la fecha actual

SELECT DATEADD(DAY, 7, GETDATE()) AS NextWeek;

• Cadenas de Texto:
  • LEN(): Devuelve la longitud de una cadena.
  • SUBSTRING(): Devuelve una subcadena.

— Obtener la longitud de una cadena

SELECT LEN(‘SQL Server’) AS StringLength;

— Obtener una subcadena

SELECT SUBSTRING(‘SQL Server’, 1, 3) AS SubString;

Use northwind go –1.Creacion de Funciones Escalares CREATE FUNCTION Iva  (@DATE money) RETURNS money AS BEGIN Declare @iva money Set  @Iva=@date*.12 Return(@iva) END –3.Revisar la funcion debe de escribirse el nombre con dos partes Select unitprice, dbo.iva(unitprice) as iva from [order details] –4.borrar la funcion drop function iva –5.Otro ejemplo de funcion CREATE Function Comision(@valor money) ReturnS money as Begin Declare @Resultado money if @valor>=15     BEGIN          SET @RESULTADO= @VALOR * 1.10     END ELSE     BEGIN          SET @RESULTADO=@VALOR     END RETURN @RESULTADO END –ejecutar la funcion SELECT UNITPRICE, DBO.Comision(UNITPRICE) FROM [ORDER DETAILS] /* en una funcion no se pueden usar estas funciones no determiniestas @@ERROR FORMATMESSAGE    IDENTITY     USER_NAME @@IDENTITY   GETANSINULL NEWID    @@ERROR @@ROWCOUNT   GETDATE PERMISSIONS @@IDENTITY @@TRANCOUNT GetUTCDate   SESSION_USER     @@ROWCOUNT APP_NAME     HOST_ID STATS_DATE   @@TRANCOUNT CURRENT_TIMESTAMP         HOST_NAME     SYSTEM_USER CURRENT_USER     IDENT_INCR   TEXTPTR DATENAME     IDENT_SEED   TEXTVALID */ –6.Una función con valores de tabla de varias instrucciones es una combinación de una vista y un procedimiento almacenado. CREATE FUNCTION LISTAPORPAIS(@PAIS  VARCHAR(50)) RETURNS @CLIENTES TABLE ( CUSTOMERID VARCHAR(10), COMPANYNAME VARCHAR(50),CONTACTNAME VARCHAR(50) ,COUNTRY VARCHAR(50) ) AS BEGIN INSERT @CLIENTES SELECT CUSTOMERID,COMPANYNAME,CONTACTNAME,COUNTRY FROM CUSTOMERS WHERE COUNTRY=@PAIS RETURN END –7.EJECUCION DE FUNCION SELECT * FROM LISTAPORPAIS(‘GERMANY’) –8.Ejemplo de una función con valores de tabla en línea CREATE FUNCTION DETALLEPEDIDO_FECHA(@INICIO DATETIME, @FIN DATETIME) RETURNS TABLE AS RETURN ( SELECT O.ORDERID,O.ORDERDATE,P.PRODUCTID, P.PRODUCTNAME,(OD.UNITPRICE * OD.QUANTITY) AS TOTAL FROM ORDERS AS O INNER JOIN [ORDER DETAILS] AS OD ON O.ORDERID=OD.ORDERID INNER JOIN PRODUCTS AS P ON OD.PRODUCTID=P.PRODUCTID WHERE O.ORDERDATE BETWEEN @INICIO AND @FIN ) –Ejecutar la funcion SELECT * FROM DBO.DETALLEPEDIDO_FECHA(‘1998-01-01’,‘1998-31-12’)