Trabalhando com SQL CLR: User Defined Function Scalar

Boa noite pessoas!

Dando seqüência aos artigos relacionados a CLR no SQL Server, partiremos para as funções (ou user defined fuction), cuja estrutura é semelhante as stored procedures, mas diferente das stored procedures, as funções podem ser utilizadas em combinação com consultas em T-SQL.

Neste primeiro artigo, veremos a forma mais simples das funções desenvolvidas em CLR, as funções escalares, que possuem como retorno “um valor”, como demonstrado a seguir:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction]
    public static SqlString Segundo()
    {
        return new SqlString("SQL From Hell!!!");
    }
};

SELECT dbo.Segundo() AS Valor

Da mesma forma que nomeamos procedimentos armazenados anteriormente, também podemos nomear funções desenvolvidas em CLR, assim como trabalhar com parâmetros:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction(Name = "UDF_Segundo")]
    public static SqlString Segundo(SqlString texto)
    {
        return texto;
    }
};

SELECT dbo.UDF_Segundo('SQL From Hell!!!') AS Valor

E estas características podem ser manipuladas com o atributo SqlFacet.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction(Name = "UDF_Segundo")]
    public static SqlString Segundo
        ([SqlFacet(MaxSize=40)]SqlString texto)
    {
        return texto;
    }
};

SELECT dbo.UDF_Segundo('SQL From Hell!!!') AS Valor

Até mesmos os resultados provenientes destas funções podem ser manipulados, como demonstrado no script gerado automaticamente pelo Visual Studio no deploy.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction(Name = "UDF_Segundo")]
    [return: SqlFacet(MaxSize=40)]
    public static SqlString Segundo
        ([SqlFacet(MaxSize=40)]SqlString texto)
    {
        return texto;
    }
};

SELECT dbo.UDF_Segundo('SQL From Hell!!!') AS Valor

--Script gerado automaticamente pelo Visual Studio no deploy
CREATE FUNCTION [dbo].[UDF_Segundo](@texto [nvarchar](40))
RETURNS [nvarchar](40) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Modelo].[UserDefinedFunctions].[Segundo]
GO

Como exemplo do que podemos desenvolver com funções escalares, demonstro abaixo uma função de validação de CPF desenvolvida em CLR, que pode ser utilizada para verificar numa tabela “pessoas físicas” quais são os registros possuem CPF inválido.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;

public partial class UserDefinedFunctions
{
    [SqlFunction]
    public static SqlBoolean UDF_ValidarCPF(SqlString numero)
    {
        if (numero.IsNull)
            return SqlBoolean.False;

        var cpf = new List<int>(11);

        foreach (var c in numero.Value)
            if (char.IsNumber(c))
                cpf.Add((int)char.GetNumericValue(c));

        if (cpf.Count != 11)
            return SqlBoolean.False;

        var v = new int[2];

        //Nota: Calcula o primeiro dígito de verificação.
        v[0] = 10 * cpf[0] + 9 * cpf[1] + 8 * cpf[2];
        v[0] += 7 * cpf[3] + 6 * cpf[4] + 5 * cpf[5];
        v[0] += 4 * cpf[6] + 3 * cpf[7] + 2 * cpf[8];
        v[0] = 11 - v[0] % 11;
        v[0] = v[0] >= 10 ? 0 : v[0];

        //Nota: Calcula o segundo dígito de verificação.
        v[1] = 11 * cpf[0] + 10 * cpf[1] + 9 * cpf[2];
        v[1] += 8 * cpf[3] + 7 * cpf[4] + 6 * cpf[5];
        v[1] += 5 * cpf[6] + 4 * cpf[7] + 3 * cpf[8];
        v[1] += 2 * v[0];
        v[1] = 11 - v[1] % 11;
        v[1] = v[1] >= 10 ? 0 : v[1];

        //Nota: Verdadeiro se os dígitos de verificação são os esperados.
        return new SqlBoolean(v[0] == cpf[9] && v[1] == cpf[10]);
    }
};

SELECT *
FROM PessoaFisica
WHERE dbo.UDF_ValidarCPF(CPF) = 0

E uma validação semelhante para CNPJ:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;

public partial class UserDefinedFunctions
{
    [SqlFunction]
    public static SqlBoolean UDF_ValidarCNPJ(SqlString numero)
    {
        if (numero.IsNull)
            return SqlBoolean.False;

        var cnpj = new List<int>(14);

        //Nota: Removendo não numéricos
        foreach (var c in numero.Value)
            if (char.IsNumber(c))
                cnpj.Add((int)char.GetNumericValue(c));

        if (cnpj.Count != 14)
            return SqlBoolean.False;

        //Nota: Dígitos de verificação.
        var v = new int[2];

        //Nota: Calcula o primeiro dígito de verificação.
        v[0] = 5 * cnpj[0] + 4 * cnpj[1] + 3 * cnpj[2] + 2 * cnpj[3];
        v[0] += 9 * cnpj[4] + 8 * cnpj[5] + 7 * cnpj[6] + 6 * cnpj[7];
        v[0] += 5 * cnpj[8] + 4 * cnpj[9] + 3 * cnpj[10] + 2 * cnpj[11];
        v[0] = 11 - v[0] % 11;
        v[0] = v[0] >= 10 ? 0 : v[0];

        //Nota: Calcula o segundo dígito de verificação.
        v[1] = 6 * cnpj[0] + 5 * cnpj[1] + 4 * cnpj[2] + 3 * cnpj[3];
        v[1] += 2 * cnpj[4] + 9 * cnpj[5] + 8 * cnpj[6] + 7 * cnpj[7];
        v[1] += 6 * cnpj[8] + 5 * cnpj[9] + 4 * cnpj[10] + 3 * cnpj[11];
        v[1] += 2 * v[0];
        v[1] = 11 - v[1] % 11;
        v[1] = v[1] >= 10 ? 0 : v[1];

        //Nota: Verdadeiro se os dígitos de verificação são os esperados.
        return new SqlBoolean(v[0] == cnpj[12] && v[1] == cnpj[13]);
    }
};

SELECT *
FROM PessoaJuridica
WHERE dbo.UDF_ValidarCNPJ(CNPJ) = 0

Assim finalizamos mais um artigo, espero que tenham gostado de conhecer um pouco mais sobre as funções “escalares”, principalmente pelos exemplos com validação de CPF e CNPJ. Lembrando também que é possível integrar estas funções com WebServices para validar e recuperar dados relacionados a CEP, telefone e muitos outros.

Agradecimentos aos artigos de CPF e CNPJ da Wikipédia pelos algoritmos de validação que adaptei em C# para as demonstrações.

Nas próximas semanas, darei uma pausa nesta série de artigos sobre SQL CLR, para atender alguns pedidos e tratar os tópicos mais votados da enquete (http://wp.me/sv9ju-enquete).