O cenário:
Para entender melhor como esta extended stored procedure funciona, vamos ao seguinte cenário.
Você trabalha em uma empresa que possui várias filiais. Todos os pedidos de compras dos clientes de sua empresa são centralizados em uma tabela na base da sua filial. Sendo assim, uma vez por mês você é solicitado a exportar para um arquivo texto os pedidos de cada filial e posteriormente enviar os arquivos às filias correspondentes.
Para simplificar nosso exemplo, estarei utilizando a tabela ORDER da base Northwind como sendo nossa tabela central. O campo Employeeid representará nossa chave para cada uma das filiais.
Para atender a esta solicitação, uma das alternativas a ser usada poderia ser a apresentada abaixo.
Definir um cursor para cada uma das filiais, navegar através de cada filial e fazer um BCP OUT dos pedidos desta filial.
O script poderia ser algo como isto:
DECLARE cc CURSOR FAST_FORWARD
FOR SELECT DISTINCT Employeeid FROM Northwind..Orders
DECLARE @employeeid INT,
@sql VARCHAR(255)
OPEN cc
FETCH NEXT FROM cc INTO @employeeid
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql = ‘BCP “SELECT * FROM Northwind..Orders WHERE Employeeid=’ + cast(@employeeid as varchar) + ‘” queryout “C:Temppedidos_filial’ + cast(@employeeid as varchar) + ‘.txt” -w -t”|” -T -S”‘ + @@servername +'”‘
–print (@sql)
EXEC master..xp_cmdshell @sql, no_output
FETCH NEXT FROM cc INTO @employeeid
END
CLOSE cc
DEALLOCATE cc
Como você pode ver o cursor é bem simples e resolve nosso problema, mas como você já deve saber, a utilização de cursores em códigos SQL não é uma prática recomenda e por outra lado, suponhamos que sua tabela possui milhões de registros e esta rotina está impactando a performance do seu sistema. Você gostaria de remover a utilização do cursor !
Poucas pessoas sabem mas tando o SQL Server 7 quanto o SQL Server 2000 fornecem uma extended stored procedure não documentada chamada xp_execresultset, que como o próprio nome diz, executa um resultset passado para ela. A xp_execresultset possui dois parâmetro Unicode, o primeiro é o statement (instrução SQL) a ser executado e o segundo é o nome da base onde o statement deverá ser executado.
Exemplo:
Use Northwind
go
— Cria uma tabela temporária para armazenar os statements do BCP
SELECT DISTINCT ‘EXEC master..xp_cmdshell ”BCP “SELECT * FROM Northwind..Orders WHERE Employeeid=’ + cast(employeeid as varchar) + ‘” queryout “C:Temppedidos_filial’ + cast(employeeid as varchar) + ‘.txt” -w -t”|” -T -S”‘ + @@servername +'””’ AS sql INTO ##TMP FROM Orders
— Executa os statements existentes na tabela temporária
EXEC master..xp_execresultset N’SELECT sql FROM ##TMP’, N’Northwind’
— Excluir a tabela temporária
DROP TABLE ##TMP
go
Agora que sabemos como usar a xp_execresultset. Como nós podemos usá-la para resolver nosso problema de performance com o cursor acima e se possível em um único statement ? A solução pode ser vista no script abaixo.
EXEC master..xp_execresultset N’SELECT
”EXEC master..xp_cmdshell ””BCP “SELECT * FROM Northwind..Orders WHERE Employeeid=”+CAST(Employeeid AS VARCHAR)+”” queryout “C:Temppedidos_filial”+CAST(Employeeid AS VARCHAR)+”.txt” -w -t”|” -T -S””+@@servername+”””” ” FROM (SELECT DISTINCT Employeeid FROM Northwind..Orders)x ‘,N’Northwind’
Como você poder ver, a xp_execresultset pode ser a solução para o nosso problema. Melhora nossa performance e elimina o uso do cursor. Sendo assim, sempre que possível faça uso desta extend.
Nota: Caso tenha ficado qualquer dúvida sobre a utilização desta extended, lembrem-se que vocês podem estar utilizando os fóruns para solucionar tais dúvidas.