Módulo 11 — Funções no SQL Server (UDFs)

1) Introdução às Funções

Funções (UDFs) encapsulam lógica reutilizável. Podem receber parâmetros e retornar um valor escalares ou uma tabela.

2) Funções Escalares

Retornam um valor único. Úteis para padronizar regras de negócio de baixo acoplamento.

2.1) Sintaxe básica

CREATE OR ALTER FUNCTION dbo.NomeDaFuncao (@Parametro Tipo)
RETURNS TipoDeRetorno
AS
BEGIN
    DECLARE @Resultado TipoDeRetorno;

    -- Processamento
    SET @Resultado = <expressão>;

    RETURN @Resultado;
END;

2.2) Exemplo — Cálculo de Idade (com ajuste de aniversário)

CREATE OR ALTER FUNCTION dbo.CalcularIdade (@DataNascimento date)
RETURNS int
AS
BEGIN
    DECLARE @Idade int = DATEDIFF(YEAR, @DataNascimento, GETDATE());
    IF (MONTH(@DataNascimento) > MONTH(GETDATE())
        OR (MONTH(@DataNascimento) = MONTH(GETDATE()) AND DAY(@DataNascimento) > DAY(GETDATE())))
        SET @Idade -= 1;
    RETURN @Idade;
END;
GO

-- Uso
SELECT Nome, dbo.CalcularIdade(DataNascimento) AS Idade
FROM dbo.Clientes;

2.3) Exemplo — Formatação de CPF

CREATE OR ALTER FUNCTION dbo.FormatarCPF (@CPF varchar(11))
RETURNS varchar(14)
AS
BEGIN
    RETURN SUBSTRING(@CPF,1,3) + '.' +
           SUBSTRING(@CPF,4,3) + '.' +
           SUBSTRING(@CPF,7,3) + '-' +
           SUBSTRING(@CPF,10,2);
END;
GO

SELECT Nome, dbo.FormatarCPF(CPF) AS CPF_Formatado
FROM dbo.Clientes;

3) Funções de Tabela — Inline (iTVF)

Escritas como um SELECT retornado diretamente. De fácil otimização, ideais para filtros reutilizáveis.

3.1) Sintaxe

CREATE OR ALTER FUNCTION dbo.ClientesPorCidade (@Cidade nvarchar(100))
RETURNS table
AS
RETURN
(
    SELECT IdCliente, Nome, Cidade, UF
    FROM dbo.Clientes
    WHERE Cidade = @Cidade
);
GO

3.2) Uso com APPLY

-- Combinar uma iTVF com cada linha de outra fonte:
SELECT v.IdVenda, v.IdCliente, c.Nome, c.Cidade
FROM dbo.Vendas AS v
CROSS APPLY dbo.ClientesPorCidade('São Paulo') AS c
WHERE c.IdCliente = v.IdCliente;

4) Funções de Tabela — Multi‑Statement (mTVF)

Permitem múltiplas operações para compor a tabela de retorno.

4.1) Sintaxe

CREATE OR ALTER FUNCTION dbo.ClientesAtivosPorEstado (@UF char(2))
RETURNS @T table
(
    IdCliente int,
    Nome nvarchar(100),
    Cidade nvarchar(100),
    UF char(2)
)
AS
BEGIN
    INSERT INTO @T (IdCliente, Nome, Cidade, UF)
    SELECT IdCliente, Nome, Cidade, UF
    FROM dbo.Clientes
    WHERE UF = @UF AND Ativo = 1;

    -- Etapas adicionais (quando necessário) podem enriquecer @T aqui.

    RETURN;
END;
GO

SELECT * FROM dbo.ClientesAtivosPorEstado('SP');

5) Parâmetros Opcionais / Padrão

Defina valores padrão para reduzir repetição.

CREATE OR ALTER FUNCTION dbo.CalcularDesconto
(
    @Valor      decimal(10,2),
    @Percentual decimal(5,2) = 10.0   -- padrão 10%
)
RETURNS decimal(10,2)
AS
BEGIN
    RETURN @Valor - (@Valor * (@Percentual / 100.0));
END;
GO

SELECT dbo.CalcularDesconto(1000, 15) AS DescontoPersonalizado; -- 850,00
SELECT dbo.CalcularDesconto(1000)      AS DescontoPadrao;       -- 900,00

6) Funções Nativas (built‑in) — com exemplos e explicações

6.1) Texto

-- LEN: comprimento (ignora espaços à direita)
SELECT LEN('ABC  ') AS Tamanho; -- 3

-- UPPER / LOWER: caixa alta/baixa
SELECT UPPER('sql server'), LOWER('T-SQL');

-- SUBSTRING + CHARINDEX: recortes localizados
DECLARE @email nvarchar(100) = N"pessoa@empresa.com";
SELECT SUBSTRING(@email, 1, CHARINDEX('@', @email)-1) AS Usuario; -- "pessoa"

-- REPLACE: substituição
SELECT REPLACE('11.222.333/0001-44', '.', '') AS SemPontos;

-- LTRIM/RTRIM / TRIM: remoção de espaços
SELECT LTRIM(RTRIM('  texto  ')) AS SemEspacos;

6.2) Numéricas

-- ABS (valor absoluto), SIGN (sinal), POWER (potência)
SELECT ABS(-10) AS Abs10, SIGN(-20) AS Sinal, POWER(2,10) AS DoisElevado10;

-- ROUND, CEILING, FLOOR
SELECT ROUND(123.4567, 2) AS Arred2, CEILING(9.01) AS PraCima, FLOOR(9.99) AS PraBaixo;

6.3) Datas

SELECT GETDATE()      AS Agora,
       SYSDATETIME()  AS AgoraPrecisao;

-- DATEDIFF / DATEADD
SELECT DATEDIFF(DAY, '2025-01-01', GETDATE()) AS DiasNoAno;
SELECT DATEADD(MONTH, 3, '2025-01-15') AS Mais3Meses;

-- EOMONTH: fim do mês
SELECT EOMONTH('2025-02-10') AS FimFevereiro;

6.4) Lógicas / Conversão

-- ISNULL / COALESCE: tratamento de nulos
SELECT ISNULL(NULL, 'valor') AS IsNullEx, COALESCE(NULL, NULL, 'primeiro não nulo');

-- NULLIF: retorna NULL se iguais
SELECT NULLIF(10, 10) AS Nulo, NULLIF(10, 5) AS Dez;

-- IIF: expressão condicional simples
SELECT IIF(5 > 3, 'maior', 'menor');

-- TRY_CONVERT: conversão segura (retorna NULL se falhar)
SELECT TRY_CONVERT(int, '123') AS Ok, TRY_CONVERT(int, 'abc') AS FalhaNull;

7) Desempenho & Boas Práticas

• Prefira iTVFs quando possível para planos otimizados.
• Evite funções em colunas no WHERE/JOIN (quebra SARGability). Considere pré‑calcular/normalizar.
• Use APPLY para parametrização linha‑a‑linha com iTVFs.
• Valide tipos de parâmetros: converta na borda com TRY_CONVERT.
• Documente e versione UDFs; prefixe com o schema (ex.: dbo.).
• Para funções em colunas calculadas persistidas/índices, avalie WITH SCHEMABINDING e determinismo.

8) Laboratório Guiado (setup + exemplos)

8.1) Tabelas de apoio

-- Ambiente de testes
IF OBJECT_ID('dbo.Vendas','U') IS NOT NULL DROP TABLE dbo.Vendas;
IF OBJECT_ID('dbo.Clientes','U') IS NOT NULL DROP TABLE dbo.Clientes;
GO

CREATE TABLE dbo.Clientes
(
    IdCliente      int            IDENTITY PRIMARY KEY,
    Nome           nvarchar(100)  NOT NULL,
    CPF            varchar(11)    NULL,
    DataNascimento date           NULL,
    Cidade         nvarchar(100)  NULL,
    UF             char(2)        NULL,
    Ativo          bit            NOT NULL DEFAULT 1
);

CREATE TABLE dbo.Vendas
(
    IdVenda     int            IDENTITY PRIMARY KEY,
    IdCliente   int            NOT NULL REFERENCES dbo.Clientes(IdCliente),
    DataVenda   date           NOT NULL,
    Valor       decimal(10,2)  NOT NULL
);
GO

INSERT INTO dbo.Clientes (Nome, CPF, DataNascimento, Cidade, UF, Ativo) VALUES
(N'João Silva', '11122233344', '1993-05-10', N'São Paulo', 'SP', 1),
(N'Ana Souza',  '55566677788', '1997-09-21', N'Campinas',  'SP', 1),
(N'Bruno Lima', NULL,          '1985-12-02', N'Curitiba',  'PR', 0);

INSERT INTO dbo.Vendas (IdCliente, DataVenda, Valor) VALUES
(1, '2025-08-01', 1200.00),
(1, '2025-08-15',  350.50),
(2, '2025-08-10',  999.90);
GO

8.2) Criação e uso das UDFs deste módulo

-- Escalar: Idade
CREATE OR ALTER FUNCTION dbo.CalcularIdade (@DataNascimento date)
RETURNS int
AS
BEGIN
    DECLARE @Idade int = DATEDIFF(YEAR, @DataNascimento, GETDATE());
    IF (MONTH(@DataNascimento) > MONTH(GETDATE())
        OR (MONTH(@DataNascimento) = MONTH(GETDATE()) AND DAY(@DataNascimento) > DAY(GETDATE())))
        SET @Idade -= 1;
    RETURN @Idade;
END;
GO

-- Escalar: Formatar CPF
CREATE OR ALTER FUNCTION dbo.FormatarCPF (@CPF varchar(11))
RETURNS varchar(14)
AS
BEGIN
    IF @CPF IS NULL OR LEN(@CPF) <> 11 OR TRY_CONVERT(bigint, @CPF) IS NULL
        RETURN NULL; -- validação simples

    RETURN SUBSTRING(@CPF,1,3) + '.' + SUBSTRING(@CPF,4,3) + '.' +
           SUBSTRING(@CPF,7,3) + '-' + SUBSTRING(@CPF,10,2);
END;
GO

-- iTVF: Clientes por cidade
CREATE OR ALTER FUNCTION dbo.ClientesPorCidade (@Cidade nvarchar(100))
RETURNS table
AS
RETURN
(
    SELECT IdCliente, Nome, Cidade, UF
    FROM dbo.Clientes
    WHERE Cidade = @Cidade
);
GO

-- mTVF: Clientes ativos por estado
CREATE OR ALTER FUNCTION dbo.ClientesAtivosPorEstado (@UF char(2))
RETURNS @T table
(
    IdCliente int,
    Nome nvarchar(100),
    Cidade nvarchar(100),
    UF char(2)
)
AS
BEGIN
    INSERT INTO @T
    SELECT IdCliente, Nome, Cidade, UF
    FROM dbo.Clientes
    WHERE UF = @UF AND Ativo = 1;

    RETURN;
END;
GO

-- Testes rápidos
SELECT Nome, dbo.FormatarCPF(CPF) AS CPF_Formatado, dbo.CalcularIdade(DataNascimento) AS Idade
FROM dbo.Clientes;

SELECT * FROM dbo.ClientesPorCidade(N'São Paulo');
SELECT * FROM dbo.ClientesAtivosPorEstado('SP');

8.3) APPLY na prática

-- Total de vendas por cliente, em SP, com nomes/tratamento via UDFs
SELECT v.IdCliente,
       c.Nome,
       dbo.FormatarCPF(c.CPF) AS CPF_Formatado,
       SUM(v.Valor) AS Total
FROM dbo.Vendas AS v
JOIN dbo.Clientes AS c ON c.IdCliente = v.IdCliente
CROSS APPLY dbo.ClientesAtivosPorEstado('SP') AS ativos
WHERE ativos.IdCliente = c.IdCliente
GROUP BY v.IdCliente, c.Nome, c.CPF;

9) Exercícios

1. Escalar — Margem de Lucro: Crie dbo.CalcularMargemLucro(@PrecoVenda, @Custo) que retorne a margem em % com duas casas.
2. iTVF — Vendas por Período: Crie dbo.VendasPorPeriodo(@DataInicial, @DataFinal) retornando IdVenda, IdCliente, DataVenda, Valor.
3. Limpeza de texto: Liste clientes exibindo Nome em maiúsculas e CPF com máscara, substituindo NULL por 'SEM CPF'.
4. Data: Mostre DataVenda e ÚltimoDiaDoMês usando EOMONTH.
5. Conversão segura: Converta uma coluna textual de números (crie uma variável de teste) usando TRY_CONVERT e some valores válidos.

10) Gabarito (sugestões)

-- 1) Escalar — Margem (%)
CREATE OR ALTER FUNCTION dbo.CalcularMargemLucro (@PrecoVenda decimal(10,2), @Custo decimal(10,2))
RETURNS decimal(5,2)
AS
BEGIN
    IF @PrecoVenda IS NULL OR @PrecoVenda = 0 RETURN NULL;
    RETURN ROUND(((@PrecoVenda - @Custo) / @PrecoVenda) * 100.0, 2);
END;
GO

-- 2) iTVF — Vendas por Período
CREATE OR ALTER FUNCTION dbo.VendasPorPeriodo (@DataInicial date, @DataFinal date)
RETURNS table
AS
RETURN
(
    SELECT IdVenda, IdCliente, DataVenda, Valor
    FROM dbo.Vendas
    WHERE DataVenda >= @DataInicial
      AND DataVenda <= @DataFinal
);
GO

-- 3) Limpeza de texto
SELECT UPPER(Nome) AS NomeMaiusculo,
       ISNULL(dbo.FormatarCPF(CPF), 'SEM CPF') AS CPF_Mascara
FROM dbo.Clientes;

-- 4) EOMONTH
SELECT DataVenda, EOMONTH(DataVenda) AS UltimoDia
FROM dbo.Vendas;

-- 5) TRY_CONVERT
DECLARE @t TABLE (ValorTxt nvarchar(10));
INSERT INTO @t VALUES (N'100'), (N'AB'), (N'250'), (N'xyz');

SELECT SUM(TRY_CONVERT(int, ValorTxt)) AS SomaValidos
FROM @t;  -- RESULTADO: 350

11) Erros Comuns & Checklist

12) Próximos Passos

• Converter UDFs úteis deste módulo em colunas calculadas (quando fizer sentido) e medir impacto.
• Explorar schema binding e computed columns persistidas.
• Preparar para o Módulo 12: Views e Indexed Views (otimização com materialização).