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 !!!