Hey guys!
Are you all right?

In this quick post, I will demonstrate once again the use of OLE Automation and CLR procedures to consume information on the Web and bring it to our SQL Server database, so that we can work with this information as we need. This time, I'm going to show you how to consult city, state, neighborhood and street information based on an entered zip code, which is widely used nowadays.

As the API return contains data in JSON format, I will use the JSON_VALUE function from SQL Server 2016. If you want to know much more about handling strings and JSON files in SQL Server 2016, visit my post SQL Server 2016 – Using native JSON support (JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON, ISJSON, JSON_MODIFY).

This article is a complement to my post How to query information from a CEP in SQL Server, where I demonstrated how to query data from a CEP using the Viacep.com.br API and which returned the data in XML format. I decided to make a new post demonstrating how to do this using another API and processing the data in JSON format.

Querying zip code data

To carry out this operation, I will use the great service on the page Bemean ZIP Code to view zip code information.

To make Web requests through SQL Server, you can use the solution using OLE Automation, which I will demonstrate below, or using the Stored Procedure stpWs_Requisicao (my preferred way), from SQL CLR (C#), which I demonstrated in the post Performing POST and GET requests using CLR (C#) in SQL Server.

Querying CEP information using OLE Automation

Now I will demonstrate how to perform this query using OLE Automation. The routine is prepared to activate the resource in the instance, if it is not activated, and deactivate it again at the end of execution.

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)

Querying CEP information using the CLR

Now I will demonstrate how to perform this query using the CLR (C#), which in my view is the best solution, although it is more complex to implement and requires knowledge of a programming language (C# or VB).

If you don't know what the CLR is and would like to learn more about this fantastic resource that can be used in SQL Server, see the post Introduction to SQL CLR (Common Language Runtime) in SQL Server. The source code of the CLR procedure stpWs_Requisicao is available in the post Performing POST and GET requests using CLR (C#) in 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)

That's it, folks!
I hope you liked this post.

Hugs and see you next time.