Olá pessoal!
Tudo bem com vocês ?

Neste post rápido, vou demonstrar mais uma vez o uso de procedures OLE Automation e CLR para consumir informações na Web e trazer para o nosso banco SQL Server, de forma que possamos trabalhar com essa informação conforme nossa necessidade. Desta vez, vou mostrar como consultar as informações de cidade, estado, bairro e logradouro a partir de um cep informado, o que é muito utilizado atualmente.

Como o retorno da API contém dados no formato JSON, vou utilizar a função JSON_VALUE do SQL Server 2016. Caso você queira saber muito mais sobre tratamento de strings e arquivos JSON no SQL Server 2016, acesse o meu post SQL Server 2016 – Utilizando o suporte nativo a JSON (JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON, ISJSON, JSON_MODIFY).

Esse artigo é um complemento do meu post Como consultar informações de um CEP no SQL Server, onde demonstrei como consultar dados de um CEP utilizando a API do Viacep.com.br e que retornava os dados no formato XML. Resolvi fazer um novo post demonstrando como fazer isso utilizando uma outra API e tratando os dados no formato JSON.

Consultando os dados do CEP

Para realizar essa operação, vou utilizar o ótimo serviço da página CEP Bemean para consultar as informações de CEP.

Para realizar as requisições Web pelo SQL Server, você pode utilizar a solução utilizando OLE Automation, que vou demonstrar abaixo ou utilizando a Stored Procedure stpWs_Requisicao (minha forma preferida), do SQL CLR (C#), que eu demonstrei no post Realizando requisições POST e GET utilizando CLR (C#) no SQL Server.

Consultando as informações do CEP utilizando OLE Automation

Agora vou demonstrar como realizar essa consulta utilizando OLE Automation. A rotina está preparada para ativar o recurso na instância, caso não esteja ativado e desativar novamente ao final da execução.

CREATE PROCEDURE dbo.stpConsulta_CEP_OLE (
    @Nr_CEP VARCHAR(20)
)
AS BEGIN

    --------------------------------------------------------------------------------
    -- Habilitando o OLE Automation (Se não estiver ativado)
    --------------------------------------------------------------------------------

    DECLARE @Fl_Ole_Automation_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'Ole Automation Procedures')
 
    IF (@Fl_Ole_Automation_Ativado = 0)
    BEGIN
 
        EXECUTE SP_CONFIGURE 'show advanced options', 1;
        RECONFIGURE WITH OVERRIDE;
    
        EXEC sp_configure 'Ole Automation Procedures', 1;
        RECONFIGURE WITH OVERRIDE;
    
    END


    DECLARE 
        @obj INT,
        @Url VARCHAR(255),
        @resposta VARCHAR(8000),
        @xml XML
 
 
    -- Recupera apenas os números do CEP
    DECLARE @startingIndex INT = 0
    
    WHILE (1=1)
    BEGIN
      
        SET @startingIndex = PATINDEX('%[^0-9]%', @Nr_CEP)  
        
        IF (@startingIndex <> 0)
            SET @Nr_CEP = REPLACE(@Nr_CEP, SUBSTRING(@Nr_CEP, @startingIndex, 1), '')  
        ELSE    
            BREAK
            
    END
    
    
    
    SET @Url = 'https://cep-bemean.herokuapp.com/api/br/' + @Nr_CEP
 
    EXEC sys.sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT
    EXEC sys.sp_OAMethod @obj, 'open', NULL, 'GET', @Url, FALSE
    EXEC sys.sp_OAMethod @obj, 'send'
    EXEC sys.sp_OAGetProperty @obj, 'responseText', @resposta OUT
    EXEC sys.sp_OADestroy @obj
    
    SELECT
        JSON_VALUE(@resposta, '$.code') AS CEP,
        JSON_VALUE(@resposta, '$.address') AS Logradouro,
        JSON_VALUE(@resposta, '$.district') AS Bairro,
        JSON_VALUE(@resposta, '$.city') AS Cidade,
        JSON_VALUE(@resposta, '$.state') AS Estado



    --------------------------------------------------------------------------------
    -- Desativando o OLE Automation (Se não estava habilitado antes)
    --------------------------------------------------------------------------------

    IF (@Fl_Ole_Automation_Ativado = 0)
    BEGIN

        EXEC sp_configure 'Ole Automation Procedures', 0;
        RECONFIGURE WITH OVERRIDE;

        EXECUTE SP_CONFIGURE 'show advanced options', 0;
        RECONFIGURE WITH OVERRIDE;

    END


END
EXEC dbo.stpConsulta_CEP_OLE
    @Nr_CEP = '29200260' -- varchar(20)

Consultando as informações do CEP utilizando o CLR

Agora vou demonstrar como realizar essa consulta utilizando o CLR (C#), que na minha visão, é a melhor solução, embora seja mais complexa para implementar e exija conhecimentos em uma linguagem de programação (C# ou VB).

Caso você não saiba o que é o CLR e gostaria de aprender mais sobre esse fantástico recurso que pode ser utilizado no SQL Server, veja o post Introdução ao SQL CLR (Common Language Runtime) no SQL Server. O código-fonte da procedure do CLR stpWs_Requisicao está disponível no post Realizando requisições POST e GET utilizando CLR (C#) no SQL Server.

CREATE PROCEDURE dbo.stpConsulta_CEP_CLR (
    @Nr_CEP VARCHAR(20)
)
AS BEGIN
 
    -- Recupera apenas os números do CEP
    DECLARE @startingIndex INT = 0
    
    WHILE (1=1)
    BEGIN
      
        SET @startingIndex = PATINDEX('%[^0-9]%', @Nr_CEP)  
        
        IF (@startingIndex <> 0)
            SET @Nr_CEP = REPLACE(@Nr_CEP, SUBSTRING(@Nr_CEP, @startingIndex, 1), '')  
        ELSE    
            BREAK
            
    END
    
    
    DECLARE 
        @Url VARCHAR(500) = 'https://cep-bemean.herokuapp.com/api/br/' + @Nr_CEP,
        @resposta NVARCHAR(MAX);


    EXEC CLR.dbo.stpWs_Requisicao
        @Ds_Url = @Url , -- nvarchar(max)
        @Ds_Metodo = N'GET' , -- nvarchar(max)
        @Ds_Parametros = N'' , -- nvarchar(max)
        @Ds_Codificacao = N'UTF-8' , -- nvarchar(max)
        @Ds_Retorno_OUTPUT = @resposta OUTPUT -- nvarchar(max)
    

    SELECT
        JSON_VALUE(@resposta, '$.code') AS CEP,
        JSON_VALUE(@resposta, '$.address') AS Logradouro,
        JSON_VALUE(@resposta, '$.district') AS Bairro,
        JSON_VALUE(@resposta, '$.city') AS Cidade,
        JSON_VALUE(@resposta, '$.state') AS Estado

END
EXEC dbo.stpConsulta_CEP_CLR
    @Nr_CEP = '29200290' -- varchar(20)

É isso aí, pessoal!
Espero que tenham gostado desse post.

Abraço e até a próxima.