Redirect

This Page

has moved to a new address:

http://www.tuliorosa.com.br

Sorry for the inconvenience…

Redirection provided by Blogger to WordPress Migration Service

quarta-feira, janeiro 27, 2010

Convertendo varchar em tabela

image

Esse post demonstra como criar uma função para transformar valores em uma string (array), em uma tabela.

Uma de suas utilizações é na passagem de array como parametros em stored procedure (SP). Em post anterior já foi demonstrado como passar a tabela como XML, nesse será demonstrado como passar uma string (varchar).

create function f_Split (@s varchar(8000) = NULL, @d char(1) = ',')
returns @tabela table (x varchar(1000))
as
begin

    ;with split(i,j) as
    (
        select i = 1, j = charindex(@d, @s + @d)
        union all
        select i = j + 1, j = charindex(@d, @s + @d, j + 1) from split where charindex(@d, @s + @d, j + 1) <> 0
    )
    insert    @tabela (x)
    select    substring(@s,i,j-i) from split option (maxrecursion 1000)

    return
end

Para fazer uso da função, podemos utiliza-la da seguinte forma:

declare @texto varchar(100)
set @texto = '10,20,30,40'

select * from dbo.f_Split(@texto,',')

Para utiliza-la para passar um array como parametro de uma SP, temos a seguinte forma:

. Montando o cenário:

create table MinhaTabela (nome varchar(50),codigo int);
insert MinhaTabela values ('Tulio',10);
insert MinhaTabela values ('Fabiola',20);
insert MinhaTabela values ('Bruno',30);

. Criando a SP de teste com erro:

create proc s_testeErrado @codigos varchar(1000)
as
    select * from MinhaTabela where codigo in (@codigos)

. Criando a SP de teste:

create proc s_teste @codigos varchar(1000)
as
    select * from MinhaTabela where codigo in (select x from dba.dbo.f_Split(@codigos,','))

. Executando as SP:

exec s_testeErrado '10,20,30'
go

exec s_teste '10,20,30'
go

Como resultado, a primeira SP irá retornar o seguinte erro:

Msg 245, Level 16, State 1, Procedure s_testeErrado, Line 4
Conversion failed when converting the varchar value '10,20,30' to data type int.

A segunda SP irá retornar o resultado esperado:

nome    codigo
Tulio    10
Fabiola    20
Bruno    30

 

Referencia: http://thiagozavaschi.spaces.live.com/Blog/cns!8DE5A8EFC1819ECA!277.entry

Um comentário:

  1. Opa Tulio beleza? Fico feliz que algo que eu tenha feito te ajudou!
    Só lembrando que tenho um novo endereço para referência ao blog: www.zavaschi.com. Pois há algum tempo atrás troquei a engine do blog pois o Spaces não me atendia!
    Grande abraço!
    Thiago Zavaschi

    ResponderExcluir