¡Hola, chicos!
¿Estás bien?

En esta publicación rápida, demostraré una vez más el uso de OLE Automation y procedimientos CLR para consumir información en la Web y llevarla a nuestra base de datos SQL Server, para que podamos trabajar con esta información según necesitemos. En esta ocasión, les mostraré cómo consultar información de ciudades, estados, vecindarios y calles en función de un código postal ingresado, que se usa ampliamente hoy en día.

Como la devolución de API contiene datos en formato JSON, usaré la función JSON_VALUE de SQL Server 2016. Si quieres saber mucho más sobre el manejo de cadenas y archivos JSON en SQL Server 2016, visita mi publicación. SQL Server 2016: uso de compatibilidad con JSON nativo (JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON, ISJSON, JSON_MODIFY).

Este artículo es un complemento a mi post. Cómo consultar información de un CEP en SQL Server, donde demostré cómo consultar datos de un CEP utilizando la API Viacep.com.br y que devolvió los datos en formato XML. Decidí hacer una nueva publicación demostrando cómo hacer esto usando otra API y procesando los datos en formato JSON.

Consultar datos del código postal

Para realizar esta operación utilizaré el gran servicio de la página. Código postal de Bemeán para ver información del código postal.

Para realizar solicitudes web a través de SQL Server, puede utilizar la solución utilizando OLE Automation, que demostraré a continuación, o utilizando el procedimiento almacenado. stpWs_Requisicao (mi forma preferida), de SQL CLR (C#), que demostré en la publicación Realizar solicitudes POST y GET usando CLR (C#) en SQL Server.

Consultar información de CEP utilizando OLE Automation

Ahora demostraré cómo realizar esta consulta utilizando OLE Automation. La rutina está preparada para activar el recurso en la instancia, si no está activado, y desactivarlo nuevamente al final de la ejecución.

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)

Consultar información de CEP mediante CLR

Ahora demostraré cómo realizar esta consulta utilizando el CLR (C#), que a mi modo de ver es la mejor solución, aunque es más complejo de implementar y requiere conocimientos de algún lenguaje de programación (C# o VB).

Si no sabes qué es el CLR y te gustaría aprender más sobre este fantástico recurso que se puede utilizar en SQL Server, consulta la publicación. Introducción a SQL CLR (Common Language Runtime) en SQL Server. El código fuente del procedimiento CLR. stpWs_Requisicao esta disponible en la publicacion Realizar solicitudes POST y GET usando CLR (C#) en 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)

¡Eso es todo, amigos!
Espero que te haya gustado esta publicación.

Un abrazo y hasta la próxima.