Enviar uma tabela como parâmetro de uma Procedure

Qualquer profissional que utiliza banco de dados SQL Server, sabe que uma store procedure podem receber um conjunto de parâmetros separados por vírgulas. Um identificador do produto, uma quantidade ou código de um cliente. Mas quem não se deparou com uma necessidade de passar como parâmetro um conjunto de dados ou uma tabela.

Nesse artigo vou demonstrar como implementar essa funcionalidade no SQL Server 2005.

Objetivo:

Atualizar o saldo de estoque dos itens do cadastro de produtos que estão gravados na tabela
Produtos com as vendas dos mesmos itens registrados na tabela ItensPedidos.

Primeiro vamos montar um ambiente para realizar a demonstração.

Estruturas das tabelas:

Create Table ItensPedido(
id int IDENTITY(1,1) NOT NULL Primary Key,
idPedido int NULL,
idProduto int NULL,
quantidade int NULL,
preco smallmoney NULL
)
GO

Create Index idxProduto on ItensPedido(idProduto)

Create Table Produto(
id int NOT NULL Primary Key,
descricao varchar(100) NULL,
unidade char(2) NULL,
estoque int NULL )
GO

Carga do dados:

insert into produto (id,descricao,unidade, estoque ) values (1,'Caneta','pc', 500)
insert into produto (id,descricao,unidade, estoque ) values (2,'Lapis','un', 800)
insert into produto (id,descricao,unidade, estoque ) values (3,'Borracha','pc', 900)
insert into produto (id,descricao,unidade, estoque ) values (4,'Caderno','pc', 700)

insert into ItensPedido (idPedido,idProduto,quantidade,preco) values (1,1,50,2.50)
insert into ItensPedido (idPedido,idProduto,quantidade,preco) values (1,2,20,1.00)
insert into ItensPedido (idPedido,idProduto,quantidade,preco) values (1,3,30,3.50)
insert into ItensPedido (idPedido,idProduto,quantidade,preco) values (2,4,10,1.00)
insert into ItensPedido (idPedido,idProduto,quantidade,preco) values (2,1,15,3.50)
insert into ItensPedido (idPedido,idProduto,quantidade,preco) values (3,2,25,1.00)
insert into ItensPedido (idPedido,idProduto,quantidade,preco) values (3,3,05,4.50)
insert into ItensPedido (idPedido,idProduto,quantidade,preco) values (3,4,35,1.00)

Entendimento dos dados:

Para entender o que será realizado, vamos tomar como exemplo o produto Caneta que tem 500 peças no estoque. Nas vendas realizadas, temos os registros dos pedidos de número 1 e 2 que venderam o produto Caneta com a quantidade 50 e 15 peças respectivamente.

No final, o produto caneta na tabela Produtos deverá ter um saldo em estoque de 435 peças.

Procedimentos:

1. Estrutura em XML do dados. Utilizamos o comando SELECT com a opção FOR XML que gera uma estrutura XML. A opção RAW determina que cada linha retornada é um elemento row com o seu apelido e a opção ROOT determina o elemento raiz da estrutura.

No exemplo abaixo, cada elemento row do XML terá um apelido Item e o elemento raiz da estrutura terá o nome Produto.

select idProduto,Quantidade
from ItensPedido
where idproduto in (1,2,3,4)
for xml raw('Item'), root('Produto')












2. Armazenar a estrutura XML em uma variável. No SQL Server 2005 tem o novo tipo de dados XML que pode ser usando para definir uma variável ou coluna de uma tabela. Nesse exemplo, a instrução SELECT já está preparada para agrupar o produto e somar a quantidade.

Declare @x xml

SET @x = (select idProduto,sum(Quantidade) as Quantidade
from ItensPedido
group by idProduto
for xml raw('Item'), root('Produto'),type)

Select @x









A opção TYPE no final do SELECT, indica que a estrutura XML deve ser retornado em um tipo de dados XML.

3. Transformar uma estrutura XML em uma estrutura relacional. O SQL Server 2005 implementa uma linguagem de consultas XML conhecida como XQUERY. Entre os vários métodos dispóniveis nessa linguagem, utilizaremos os métodos nodes() e value() que transformará o XML em linhas e colunas passando a sua estrutura hierárquica para relacional.

Declare @x xml

SET @x = (select idProduto,sum(Quantidade) as Quantidade
from ItensPedido
group by idProduto
for xml raw('Item'), root('Produto')
)

Select
x.item.value('@idProduto[1]', 'int') AS idProduto,
x.item.value('@Quantidade[1]', 'INT') AS Quantidade
from @x.nodes('//Produto/Item') AS x(item)













Para maiores informações sobre a linguagem XQUERY, acesse o link do Gustavo Maia Aguiar. Nele temos detalhes e exemplos práticos dessa linguagem.

4. Montagem de procedure que recebe uma estrutura XML e atualiza os dados em uma tabela. Com base nas demonstrações acima, é possível criar uma procedure que recebe um parâmetro do tipo XML é processarmos essa estrutura com um update em outra tabela.

Alter Procedure stp_AtualizaEstoque
(@xTabela XML)
AS
Set nocount on
Update Produto SET
Estoque = Estoque - xTabela.Quantidade
FROM Produto JOIN
(SELECT
xTabela.item.value('@idProduto[1]', 'int') AS idProduto,
xTabela.item.value('@Quantidade[1]', 'int') AS Quantidade
FROM @xTabela.nodes('//Produto/Item') AS xTabela(item)
) xTabela
ON Produto.id = xTabela.idProduto
RETURN

5. Agora vamos executar o que foi proposto no início do artigo que é atualizar a tabela Produtos com os valores da tabela ItensPedido.

-- Visualização do posição atual do estoque.
SELECT * FROM Produto


DECLARE @xTabela XML

-- Montagem de uma estrutura XML do dados
SET @xTabela = (Select idProduto,sum(Quantidade) as Quantidade
from ItensPedido
group by idProduto
for xml raw('Item'), root('Produto')
)

-- Execução da procedure com a passagem do parâmetro do tipo XML.
EXECUTE stp_AtualizaEstoque @xTabela

-- Verificação do resultado.
SELECT * FROM Produto

















6. Conclusão.

É claro que para atualizar os dados de uma tabela para outra, podemos fazer direto a atualização, utilizando a junção das duas tabelas com o comando UPDATE sem a necessidade de aplicarmos o XML.

A idéia desse artigo é demonstrar o que pode ser aplicado em casos quando não sabemos como os dados estão estruturados na origem, mas sabemos que informações precisamos. Nesse caso podemos solicitar que os dados sejam enviados em estrutura XML e eles são processados pela procedure.

O SQL Server 2008 já implementa procedure que aceitam uma tabela como parâmetro e será apresentando em um próximo artigo.

É isso ai !!!

Instrução GO do Editor de Query do SQL Server 2005

Uma novidade do editor de query do SQL Server 2005 é a instrução GO, que agora aceita um número que determina quantas vezes o bloco será executado.

Exemplo:

SELECT GETDATE()
GO 2

Beginning execution loop

-----------------------
2008-04-14 23:12:37.687

(1 row(s) affected)

-----------------------
2008-04-14 23:12:37.700

(1 row(s) affected)

Batch execution completed 2 times.

Outro exemplo:

Select Getdate()
go 2
Select 'Teste da instrução GO'
go 3

Beginning execution loop

-----------------------
2008-04-14 23:14:09.577

(1 row(s) affected)

-----------------------
2008-04-14 23:14:09.577

(1 row(s) affected)

Batch execution completed 2 times.
Beginning execution loop

---------------------
Teste da instrução GO

(1 row(s) affected)

---------------------
Teste da instrução GO

(1 row(s) affected)

---------------------
Teste da instrução GO

(1 row(s) affected)

Batch execution completed 3 times.

Veja que nesse caso ele executou 3 vezes a instrução após o primeiro GO e antes do segundo.

Um exemplo prático:

- Preencher uma tabela com dados aleatórios.

Create Table TesteCarga
(id int identity(1,1), ValorRand int , Flag bit )
go

insert into TesteCarga (ValorRand) values (rand()*100)
go 10

select * from TesteCarga
go
















Vamos criar uma trigger exemplo, somente para verificar um erro que pode ocorrer na atulização de uma tabela e como podemos contornar com a instrução GO.
Essa trigger é somente para demonstração e simular um erro. O seu código não tem funcionalidade que agregue valor de conhecimento.

Create Trigger TRGTeste on TesteCarga
for update
as
begin
declare @id int
set @id = rand(10) + (select valorrand from inserted)
end
go

update TesteCarga set flag = 0
go

Msg 512, Level 16, State 1, Procedure TRGTeste, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

O erro ocorreu por que a subquery dentro da trigger está retornando mais de um valor quando utilizado em uma expressão. Isso ocorreu porque o comando update executou a atulização da coluna FLAG para todas as linhas e gerau o tabela INSERTED com as 10 ocorrências.

Para resolver o problema, o correto é fazer a atualização linha por linha. No exemplo abaixo, isso é possivel com a instrução GO

update TesteCarga set flag = 0
where id in (select top 1 id from TesteCarga where flag is null )
go 10

Como a tabela TesteCarga tem a coluna FLAG com o valor NULL para todas as linhas e ela tem um identificador único, então é selecionado uma única ocorrência dessa tabela para a coluna FLAG igual a NULL.
Essa única ocorrência é passada para o comando update que atualiza apenas uma linha. Quando a instrução GO executa o comando pela segunda vez, ele não carregará mais esse linha, já que a FLAG não tem o valor NULL e sim a próxima que atenda a condição.

select * from TesteCarga

Como retirar acento de caracteres

Por uma necessidade de regra de negócio ou para exportar dados para outras fontes de dados, as vezes precisamos tirar acento dos caracteres de certas colunas ou expressões.

Existem várias formas para resolver esses casos. Podemos criar uma procedure ou função com uma estrutura do tipo DE/PARA e desenvolver um código que captura a informação passada como parâmetro, lêr cada caracter dessa expressão e faz a troca de acordo com a estrutura DE/PARA.
O processo consiste em fazer a troca de um caracter acentuado por outro caracter sem o acento.

A proposta aqui é mostra a utilização do comando COLLATE para fazer a troca dos caracteres acentuados. Seja o exemplo:

Declare @cExpressao varchar(30)
Set @cExpressao = 'aeiouáéíóúàèìòòâêîôûãõäëïöüç'
Select @cExpressao collate sql_latin1_general_cp1251_ci_as

------------------------------
aeiouaeiouaeiooaeiouaoaeiouc

(1 row(s) affected)

Declare @cExpressao varchar(30)
Set @cExpressao = 'AEIOUÁÉÍÓÚÀÈÌÒÒÂÊÎÔÛÃÕÄËÏÖÜÇ'
Select @cExpressao collate sql_latin1_general_cp1251_ci_as

------------------------------
AEIOUAEIOUAEIOOAEIOUAOAEIOUC

(1 row(s) affected)


Podemos criar uma função que contém a regra acima, conforme exemplo abaixo:

Create Function fnTiraAcento (@cExpressao varchar(100))
Returns varchar(100)
as
Begin

Declare @cRetorno varchar(100)

Set @cRetorno = @cExpressao collate sql_latin1_general_cp1251_cs_as
Return @cRetorno

End

GO

Declare @cExpressao varchar(30)

Set @cExpressao = 'AEIOUÁÉÍÓÚÀÈÌÒÒÂÊÎÔÛÃÕÄËÏÖÜÇ'
Select dbo.fnTiraAcento( @cExpressao )

Set @cExpressao = 'aeiouáéíóúàèìòòâêîôûãõäëïöüç'
Select dbo.fnTiraAcento( @cExpressao )

----------------------------
AEIOUAEIOUAEIOOAEIOUAOAEIOUC

(1 row(s) affected)

----------------------------
aeiouaeiouaeiooaeiouaoaeiouc

(1 row(s) affected)

Se voce tem informações em uma coluna do tipo nchar e nvarchar , voce deve passar os dados para um tipo char e varchar. Exemplo:

Declare @cExpressao nvarchar(30)
Set @cExpressao = 'aeiouáéíóúàèìòòâêîôûãõäëïöüç'
Select @cExpressao collate sql_latin1_general_cp1251_ci_as

------------------------------
aeiouáéíóúàèìòòâêîôûãõäëïöüç

(1 row(s) affected)

Não ocorreu a troca dos acentos. Fazendo a conversão implícita do tipo unicode, voce consegue fazer a troca dos acentos.

Declare @cExpressao nvarchar(30)
Declare @cExpr varchar(30)
Set @cExpressao = 'aeiouáéíóúàèìòòâêîôûãõäëïöüç'
Set @cExpr = @cExpressao
Select @cExpr collate sql_latin1_general_cp1251_ci_as

------------------------------
aeiouaeiouaeiooaeiouaoaeiouc

(1 row(s) affected)

Utilizando a função criada no exemplo anterior, não temos a necessidade de fazer a conversão.
Como o parametro da função não é do tipo unicode, no momento da passagem a expressão caracter, ocorre a conversão implícita.

Declare @cExpressao nvarchar(30)
Set @cExpressao = 'aeiouáéíóúàèìòòâêîôûãõäëïöüç'
Select dbo.fnTiraAcento( @cExpressao )

----------------------------
aeiouaeiouaeiooaeiouaoaeiouc

(1 row(s) affected)

O Collate utilizado nesse exemplo (sql_latin1_general_cp1251_cs_as) foi escolhido entre 315 'collates' existente no SQL SERVER 2005 que fizeram a conversão dos conjuntos de caracteres acima.

A relação de collate é obtida atraves de função ::fn_helpcollations()

select * from ::fn_helpcollations()

Para maiores detalhes sobre COLLATE, consulte o BOL do SQLServer. Abaixo segue link sobre o assunto:

http://imasters.uol.com.br/artigo/262

Que se faça o SELECT

Olá a todos,

Meu objetivo com esse blog é mostrar as atividades do dia-a-dia de quem trabalha com banco de dados MSSQL. Ele será focado em dicas e truques, sem se importa com o nível de conhecimento de quem trabalha com banco de dados. Quero mostrar as atividades que exerce um DBA em diversas situações.

Espero que esse blog seja uma fonte de ajuda e de conhecimento para todos.

Wolney.