Como realizar pesquisas em Stored Procedures no SQL Server 2005

Boa Noite Pessoal,

Hoje no fórum do MSDN apareceu uma dúvida muito interessante. A intenção da dúvida era juntar o resultado retornado por uma stored procedure com outro resultado através de um operador UNION. A descrição da dúvida está disponível no link abaixo:

Procedures com Union
http://forums.microsoft.com/MSDN-BR/ShowPost.aspx?PostID=4197141&SiteID=21

A situação é interessante, mas como não tenho o código fonte da procedure, nem as tabelas e nem os dados, vou reproduzir minha própria situação demonstrando como realizar consultas aproveitando o resultado de stored procedures. Para isso irei criar uma simples procedure no SQL Server 2005 que retorna o nome de todos os bancos de dados. A procedure está localizada no banco de dados MASTER, mas esse detalhe não invalida nenhuma solução. Apenas coloquei em um banco comum a qualquer instalação do SQL Server.

USE Master
GO
CREATE PROCEDURE uspBancos
AS
SELECT
Database_ID, Name, State_Desc FROM sys.databases

Dado que o T-SQL não dá suporte a consultas em resultados retornados por stored procedures, a solução mais trivial é de fato criar uma tabela temporária com o resultado da stored procedure e realizar a consulta na tabela temporária. Ex:

— Cria a tabela temporária
CREATE TABLE #tblBancos (Database_ID INT, Name SYSNAME, State_Desc NVARCHAR(60))

— Insere o resultado da Stored Procedure
INSERT INTO #tblBancos EXEC uspBancos

— Retorna os resultados em uma instrução SELECT
SELECT Database_ID, Name, State_Desc FROM #tblBancos

— Exclui a tabela
DROP TABLE #tblBancos

Uma variação dessa solução que é portável a partir do 2005 é a utilização de variáveis do tipo TABLE. Embora não seja muito comum visto o enorme legado do SQL Server 2000, é possível usar a mesma técnica com variáveis do tipo TABLE.

— Cria a variável do tipo TABLE
DECLARE @tblBancos TABLE (Database_ID INT, Name SYSNAME, State_Desc NVARCHAR(60))

— Insere o resultado da Stored Procedure
INSERT INTO @tblBancos EXEC uspBancos

— Retorna os resultados em uma instrução SELECT
SELECT Database_ID, Name, State_Desc FROM @tblBancos

Uma solução que achei bem interessante, foi a postada pelo Fabiano Neves Amorim. A solução dele realiza uma conexão via OPENROWSET no próprio servidor. É uma solução bem criativa, mas que tem alguns pré-requisitos como habilitar consultas do tipo OPENROWSET. Adaptando-a para a procedure uspBancos, tem-se o seguinte (é necessário trocar o nome do servidor):

— Habilitar o uso de Consultas ADHOC Distribuídas
EXEC sp_configure ‘Ad Hoc Distributed Queries’ , 1

— Forçar com que as alterações tenham efeito imediato
RECONFIGURE WITH OVERRIDE

— Executar uma chamada remota via Trusted Connection
SELECT a.*
  FROM OPENROWSET(‘SQLNCLI’, ‘Server=NomeDoServidor;Trusted_Connection=yes;’,
                  ‘exec master.dbo.uspBancos’) AS a

A solução que proponho baseia-se em uma idéia bem parecida com a do Fabiano, se o T-SQL não dispõe de uma forma de consultar a stored é uma boa idéia fazer a pesquisa remotamente e retornar o resultado. O problema do OPENROWSET é que além de ser necessário habilitar consultas ADHOC distribuídas é preciso também ter cautela com a string de conexão. No exemplo postado, a conexão foi feita via Trusted Connection, mas não é possível garantir esse cenário sempre.

Toda vez que o SQL Server é instalado, ele mantém um catálogo de servidores próprio. Esse catálogo conterá os servidores utilizados em replicação, os Linked Servers, os Remote Servers, etc. Pode ser que o SQL Server em questão não tenha nenhum desses servidores, mas ele sempre terá uma entrada para si próprio. Basta consultar a View sys.servers e perceber que o servidor cujo o server_id é igual a zero é uma referência para o próprio servidor.

Todas as referências dessa tabela são Linked Servers em potencial (ainda que não apareçam na relação de Linked Servers) e podem ser usadas como tal. Assim sendo basta fazer uma consulta com o OPENQUERY e a procedure será retornada através de um SELECT e uma consulta distribuída (trocando o nome do servidor).

SELECT * FROM OPENQUERY([NomeDoServidor], ‘exec master.dbo.uspBancos’)

Provavelmente uma mensagem de erro deve ser retornada após a execução da consulta:

Msg 7411, Level 16, State 1, Line 1
Server ‘NomeDoServidor’ is not configured for DATA ACCESS.

De fato a utilização do próprio servidor não está habilitada para acesso a dados, para habilitar, basta rodar a procedure sp_serveroption e liberar esse acesso (trocando o nome do servidor).

EXEC sp_serveroption ‘NomeDoServidor’, ‘Data Access’, ‘true’

Após executar essa procedure, o acesso a dados estará liberado e o comando com o OPENQUERY pode ser executado sem problemas. Uma pergunta natural é se essa técnica funcionaria para usuários que não estejam na role SysAdmin. A resposta é sim. Uma vez que esse servidor funcione como um Linked Server, bastaria utilizar as procedures para configurar eventuais usuários e mapeamentos através da procedure sp_addlinkedsrvlogin.

A idéia de transformar uma stored procedure em uma consulta SELECT parece ser bem interessante (principalmente porque o OPENQUERY não exibe fragilidades de segurança), mas não acho que isso deva se tornar uma prática. Essa construção tem um forte acoplamento uma vez que o OPENQUERY irá utilizar o nome explícito do servidor. Se houver uma troca de servidor por exemplo, diversas consultas irão parar de funcionar, pois, referenciarão o servidor antigo.

Outro ponto negativo é o fato de que a consulta é feita de forma remota. Mesmo que uma cláusula WHERE esteja presente para filtrar os registros, será necessário retornar todos os registros da SP e de forma remota e isso pode ter impacto no desempenho. Se a quantidade de registros for pequena, a utilização de tabelas temporárias tende a ser superior.

Utilizar a procedure como uma instrução SELECT pode ter benefícios como utilização em cursores, joins com outras tabelas ou o uso de operadores de conjunto (UNION, EXCEPT, INTERSECT, etc), mas se esse for o caso, considere utilizar a tabela temporária ou reescrever a procedure na forma de uma User Defined Function. Possivelmente será mais performático e menos acoplado. Esse recurso só deve ser utilizado em último caso e de forma controlada (o DBA pode utilizá-lo para combinar várias SPs de catálogo por exemplo). Liberar esse recurso para utilização indiscriminada por ter sérias conseqüências. O melhor mesmo será quando alguma versão futura do SQL Server nos presentear com uma funcionalidade nativa para fazer isso.

[ ]s,

Gustavo

5 Respostas para “Como realizar pesquisas em Stored Procedures no SQL Server 2005

  1.  
    Que riqueza de material heim?
    Fiquei feliz em saber que uma simples duvida é capaz de gerar tanta informação.
    legal legal
    [ ]\’s
    Fabixx
     

  2. Cara, absolutamente fantástico.Precisava fazer um "count" nos resultados retornados por uma Procedure e não tinha nem idéia de como fazer…Adaptando o exemplo de Table mostrado eu consegui fazer o que eu precisava…Valeu mesmo pela dica parabéns…Aliás, Gustavo, já perdi a conta da quantidade de vezes que seus exemplos no MSDN e também seus artigos nos blogs me ajudaram…Valeu mesmo cara…Até mais.

  3. Olá Flávio,Que bom que meus exemplos puderam ajudá-lo. É sempre gratificante receber esse tipo de feedback.[ ]s,

  4. Os meus parabéns pelo artigo, muito bom e o único que encontrei a explicar tudo como deve ser….. muito bom….e me ajudou muito mesmo, à mais de 3 dias que andava a procura deste artigo…obrigado

    Pedro

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s