Neste artigo estaremos conhecendo um pouco sobre as funções tabulares, que consistem basicamente de funções que podem ser “selecionáveis” como as tabelas e views e ainda podem receber parâmetros.
Mas para que iremos utilizar uma função que retorne um resultado em formato de tabela? Não se preocupe, pois em algum momento você encontrará uma utilidade! Por enquanto vamos conhecer a estrutura de nossa função tabular.
A primeira característica é o a utilização de dois métodos para defini-la, sendo um método principal que será responsável por retornar um enumerador de elementos de qualquer tipo e um método auxiliar que terá o papel de traduzir estes elementos para o SQL Sever.
O método principal definirá pelo atributo “TableDefinition” a estrutura básica da “tabela de retorno” e pelo atributo “FillRowMethodName” o método responsável por transformar cada elemento do enumerador resultante do método principal como “linhas” da tabela!!!
Parece um pouco complexo? Para diminuir o susto, vamos a um exemplo simples…
Com o método principal estarei retornando um array de strings e com o método auxiliar digo que cada string que retornar será a coluna “nome” do meu resultado.
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
public partial class UserDefinedFunctions
{
[SqlFunction(Name = "UDF_Segundo"
, FillRowMethodName = "Fill"
, TableDefinition = "Nome VARCHAR(10)")]
public static IEnumerable Segundo()
{
return new[] { "Paulo", "Roberto", "Pereira", "Junior" };
}
public static void Fill(object obj, out SqlString nome)
{
nome = (string)obj;
}
};
SELECT * FROM UDF_Segundo()
O método auxiliar terá como parâmetro de entrada um objeto que representará um dos elementos resultantes do método principal, e mais um parâmetro de saída para cada coluna do resultado tabular.
Para deixar um pouco mais complexo, elaborei um segundo exemplo trabalhando com dicionário e mais de uma coluna.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Collections.Generic;
public partial class UserDefinedFunctions
{
[SqlFunction(Name = "UDF_Segundo"
, FillRowMethodName = "Fill"
, TableDefinition = "Codigo INT, Nome NVARCHAR(40)")]
public static IEnumerable Segundo()
{
var dic = new Dictionary<int, string>
{
{1, "Paulo"},
{2, "Thiago"},
{3, "João"},
{4, "Lucas"}
};
return dic;
}
public static void Fill(object obj
, out SqlInt32 codigo
, out SqlString nome )
{
var row = (KeyValuePair<int, string>)obj;
codigo = row.Key;
nome = row.Value;
}
};
SELECT * FROM UDF_Segundo()
O método auxiliar pode ser utilizado por um ou vários métodos principais, o que ajuda e muito na hora de tratar funções parecidas.
Para um exemplo muito útil de funções tabulares, elaborei uma função de “Split”, que basicamente dividi um texto de acordo com o delimitador, demonstrando assim a utilização de parâmetros na função principal, além da possibilidade de utilizar “yield” (que poderá ser útil para soluções mais complexas):
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
public partial class UserDefinedFunctions
{
[SqlFunction(FillRowMethodName = "UDF_Split_Fill"
, TableDefinition = "Texto NVARCHAR(MAX)")]
public static IEnumerable UDF_Split(
[SqlFacet(MaxSize = -1)]SqlString texto,
[SqlFacet(MaxSize = 1)]SqlString delimitador)
{
var lim = delimitador.Value[0];
var parts = texto.Value;
foreach (var item in parts.Split(lim))
{
var part = item.Trim();
if (part != string.Empty)
yield return part;
}
}
public static void UDF_Split_Fill(
object obj
, out SqlString texto)
{
texto = (string)obj;
}
};
SELECT * FROM UDF_Split('1;1.10;1.12;2;2.3;', ';')
E para a utilização de uma função de split, um cenário onde temos uma tabela de “contratos” e nesta mesma tabela os serviços de cada contrato em um campo separado por vírgulas, no qual podemos utilizar CROSS APPLY com a função de split para obter uma relação dos contratos e seus serviços:
DECLARE @Contrato TABLE(
Id INT IDENTITY,
Contrato VARCHAR(250),
Serviços VARCHAR(100)
)
INSERT INTO @Contrato
VALUES('Curitiba', '1,2,4,3')
INSERT INTO @Contrato
VALUES('Florianópolis', '3,2,8,1,4,6')
INSERT INTO @Contrato
VALUES('Campo Largo', '1,4')
SELECT S.Texto AS Serviço, C.Contrato
FROM @Contrato C
CROSS APPLY UDF_Split(C.Serviços, ',') AS S
Com este artigo, abrimos mais um pouco o leque de possibilidade do suporte CLR do SQL Server, espero que tenham gostado. No próximo artigo sobre SQL CLR, estaremos explorando funções de agregação.
SQL From Hell.com 