Como calcular o valor do frete e prazo de entrega utilizando WebService dos Correios no SQL Server

Post Views 5,217 views

Neste artigo

Reading time 3 minutes

Olá, pessoal!
Boa tarde!

Neste post, vou demonstrar mais uma vez a utilização de OLE Automation no SQL Server para consumir os dados de um serviço da Web. Dessa vez, vou consumir o serviço dos Correios para calcular prazo e valor do frete (muito utilizado pra e-Commerce).

Conhecendo o WebService dos Correios

Antes de iniciarmos a criação da Stored Procedure que irá consultar os dados, precisamos primeiro entender os parâmetros e flags do serviço dos Correios.

WebService Correios - Parâmetros de Entrada

Campo	Tipo	Obrigatório	Descrição
nCdEmpresa	String	Não. O parâmetro tem que ser passado mesmo vazio.	Seu código administrativo junto à ECT. O código está disponível no corpo do contrato firmado com os Correios.
sDsSenha	String	Não. O parâmetro tem que ser passado mesmo vazio.	Senha para acesso ao serviço associada ao seu código administrativo. A senha inicial corresponde aos 8 primeiros dígitos do CNPJ informado no contrato.
nCdServico	String	Yes	Código do serviço
sCepOrigem	String	Yes	CEP de Origem sem hífen.Exemplo: 05311900
sCepDestino	String	Yes	CEP de Destino sem hífen
nVlPeso	Decimal	Yes	Peso da encomenda incluindo sua embalagem. O peso deve ser informado em quilogramas.
nCdFormato	Int	Yes	Formato da encomenda (incluindo embalagem). 1 – Formato caixa/pacote / 2 – Formato rolo/prisma
nVlComprimento	Decimal	Yes	Comprimento da encomenda (incluindo embalagem) em centímetros.
nVlAltura	Decimal	Yes	Altura da encomenda (incluindo embalagem) em centímetros.
nVlLargura	Decimal	Yes	Largura da encomenda (incluindo embalagem) em centímetros.
nVlDiametro	Decimal	Yes	Diâmetro da encomenda (incluindo embalagem) em centímetros.
sCdMaoPropria	String	Yes	Indica se a encomenda será entregue com o serviço adicional mão própria. Valores possíveis: S ou N (S – Sim / N – Não)
nVlValorDeclarado	Decimal	Yes	Indica se a encomenda será entregue com o serviço adicional valor declarado. Neste campo deve ser apresentado o valor declarado desejado em Reais.
sCdAvisoRecebimento	String	Yes	Indica se a encomenda será entregue com o serviço adicional aviso de recebimento. Valores possíveis: S ou N (S – Sim / N – Não)
StrRetorno	String	Yes	Indica a forma de retorno da consulta. XML = Resultado em XML / Popup = Resultado em uma janela popup / <URL> = Resultado via post em uma página do requisitante

WebService Correios - Parâmetros de Retorno

Parâmetro de Saída	Descrição
Codigo	Código do Serviço de Entrega.
ValorValorDeclarado	Preço do serviço adicional Valor Declarado.
Valor	Preço total da encomenda em Reais incluindo os preços dos serviços opcionais.
ValorMaoPropria	Preço do serviço adicional Mão Própria.
ValorAvisoRecebimento	Preço do serviço adicional Aviso de Recebimento.
PrazoEntrega	Prazo estimado em dias para entrega do produto. Se o valor retornado for 0 (zero) indica que o prazo não foi retornado corretamente.
EntregaDomiciliar	Informa se a localidade informada possui entrega domiciliária. Se o prazo não for retornado corretamente o retorno deste parâmetro será vazio.
EntregaSabado	Informa se a localidade informada possui entrega domiciliária aos sábados. Se o prazo não for retornado corretamente o retorno deste parâmetro será vazio.
Erro	Os mesmos do Web Services.
MsgErro	Retorna a descrição do erro gerado.

WebService Correios - Códigos de Serviço

Códigos de Serviço
41106	PAC sem contrato
40010	SEDEX sem contrato
40045	SEDEX a Cobrar sem contrato
40126	SEDEX a Cobrar com contrato
40215	SEDEX 10 sem contrato
40290	SEDEX Hoje sem contrato
40096	SEDEX com contrato
40436	SEDEX com contrato
40444	SEDEX com contrato
81019	e-SEDEX com contrato
41068	PAC com contrato
40568	SEDEX com contrato
40606	SEDEX com contrato
81868	(Grupo 1) e-SEDEX com contrato
81833	(Grupo 2) e-SEDEX com contrato
81850	(Grupo 3) e-SEDEX com contrato

Criando a Stored Procedure para consultar os dados

Com o trecho de código abaixo, iremos criar a Stored Procedure que iremos utilizar para consultar os dados de valor do frete e prazo de entrega utilizando procedures OLE Automation para realizar a requisição GET e retornar o XML da Web, que iremos ler utilizando XQuery.

CREATE PROCEDURE dbo.stpConsulta_Frete_Correios (
    @sCepOrigem VARCHAR(12),
    @sCepDestino VARCHAR(12),
    @nCdServico INT,
    @nVlPeso FLOAT = 0.1,
    @nCdFormato SMALLINT = 1,
    @nVlComprimento INT = 20,
    @nVlAltura INT = 5,
    @nVlLargura INT = 15,
    @nVlDiametro INT = 0,
    @CdMaoPropria CHAR(1) = 'n',
    @nVlValorDeclarado FLOAT = 0,
    @CdAvisoRecebimento CHAR(1) = 'n'
)
AS BEGIN
 
 
    DECLARE 
        @obj INT,
        @Url VARCHAR(500),
        @resposta VARCHAR(8000),
        @xml XML
        
        
        
    -- Recupera apenas os números do CEP de Origem
    DECLARE @startingIndex INT = 0
    
    WHILE (1=1)
    BEGIN
      
        SET @startingIndex = PATINDEX('%[^0-9]%', @sCepOrigem)  
        
        IF (@startingIndex <> 0)
            SET @sCepOrigem = REPLACE(@sCepOrigem, SUBSTRING(@sCepOrigem, @startingIndex, 1), '')  
        ELSE    
            BREAK
            
    END
    
    
    
    -- Recupera apenas os números do CEP de Destino
    SET @startingIndex = 0
    
    WHILE (1=1)
    BEGIN
      
        SET @startingIndex = PATINDEX('%[^0-9]%', @sCepDestino)  
        
        IF (@startingIndex <> 0)
            SET @sCepDestino = REPLACE(@sCepDestino, SUBSTRING(@sCepDestino, @startingIndex, 1), '')  
        ELSE    
            BREAK
            
    END
        
 
 
    SET @Url = 'http://ws.correios.com.br/calculador/CalcPrecoPrazo.aspx?' + 
    'sCepOrigem=' + @sCepOrigem + 
    '&sCepDestino=' + @sCepDestino + 
    '&nVlPeso=' + CAST(@nVlPeso AS VARCHAR(20)) + 
    '&nCdFormato=' + CAST(@nCdFormato AS VARCHAR(20)) + 
    '&nVlComprimento=' + CAST(@nVlComprimento AS VARCHAR(20)) + 
    '&nVlAltura=' + CAST(@nVlAltura AS VARCHAR(20)) + 
    '&nVlLargura=' + CAST(@nVlLargura AS VARCHAR(20)) + 
    '&sCdMaoPropria=' + @CdMaoPropria + 
    '&nVlValorDeclarado=' + CAST(@nVlValorDeclarado AS VARCHAR(20)) + 
    '&sCdAvisoRecebimento=' + @CdAvisoRecebimento + 
    '&nCdServico=' + CAST(@nCdServico AS VARCHAR(20)) + 
    '&nVlDiametro=' + CAST(@nVlDiametro AS VARCHAR(20)) + '&StrRetorno=xml'
 
    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
    
    SET @xml = @resposta COLLATE SQL_Latin1_General_CP1251_CS_AS
    
    
    SELECT
        @xml.value('(/Servicos/cServico/Codigo)[1]', 'bigint') AS Codigo_Servico,
        @xml.value('(/Servicos/cServico/Valor)[1]', 'varchar(20)') AS Valor_Frete,
        @xml.value('(/Servicos/cServico/PrazoEntrega)[1]', 'int') AS Prazo_Entrega,
        @xml.value('(/Servicos/cServico/ValorSemAdicionais)[1]', 'varchar(20)') AS Valor_Sem_Adicionais,
        @xml.value('(/Servicos/cServico/ValorMaoPropria)[1]', 'varchar(20)') AS Valor_Entrega_Em_Maos,
        @xml.value('(/Servicos/cServico/ValorAvisoRecebimento)[1]', 'varchar(20)') AS Valor_Aviso_Recebimento,
        @xml.value('(/Servicos/cServico/EntregaDomiciliar)[1]', 'varchar(20)') AS Entrega_Domiciliar,
        @xml.value('(/Servicos/cServico/EntregaDomiciliar)[1]', 'varchar(1)') AS Entrega_Domiciliar,
        @xml.value('(/Servicos/cServico/EntregaSabado)[1]', 'varchar(1)') AS Entrega_Sabado,
        @xml.value('(/Servicos/cServico/Erro)[1]', 'int') AS Codigo_Erro,
        @xml.value('(/Servicos/cServico/MsgErro)[1]', 'varchar(500)') AS Mensagem_Erro
        
 
END

CREATE PROCEDURE dbo.stpConsulta_Frete_Correios (

@sCepOrigem VARCHAR(12),

@sCepDestino VARCHAR(12),

@nCdServico INT,

@nVlPeso FLOAT = 0.1,

@nCdFormato SMALLINT = 1,

@nVlComprimento INT = 20,

@nVlAltura INT = 5,

@nVlLargura INT = 15,

@nVlDiametro INT = 0,

@CdMaoPropria CHAR(1) = 'n',

@nVlValorDeclarado FLOAT = 0,

@CdAvisoRecebimento CHAR(1) = 'n'

)

AS BEGIN

DECLARE

@obj INT,

@Url VARCHAR(500),

@resposta VARCHAR(8000),

@xml XML

-- Recupera apenas os números do CEP de Origem

DECLARE @startingIndex INT = 0

WHILE (1=1)

BEGIN

SET @startingIndex = PATINDEX('%[^0-9]%', @sCepOrigem)

IF (@startingIndex <> 0)

SET @sCepOrigem = REPLACE(@sCepOrigem, SUBSTRING(@sCepOrigem, @startingIndex, 1), '')

ELSE

BREAK

END

-- Recupera apenas os números do CEP de Destino

SET @startingIndex = 0

WHILE (1=1)

BEGIN

SET @startingIndex = PATINDEX('%[^0-9]%', @sCepDestino)

IF (@startingIndex <> 0)

SET @sCepDestino = REPLACE(@sCepDestino, SUBSTRING(@sCepDestino, @startingIndex, 1), '')

ELSE

BREAK

END

SET @Url = 'http://ws.correios.com.br/calculador/CalcPrecoPrazo.aspx?' +

'sCepOrigem=' + @sCepOrigem +

'&sCepDestino=' + @sCepDestino +

'&nVlPeso=' + CAST(@nVlPeso AS VARCHAR(20)) +

'&nCdFormato=' + CAST(@nCdFormato AS VARCHAR(20)) +

'&nVlComprimento=' + CAST(@nVlComprimento AS VARCHAR(20)) +

'&nVlAltura=' + CAST(@nVlAltura AS VARCHAR(20)) +

'&nVlLargura=' + CAST(@nVlLargura AS VARCHAR(20)) +

'&sCdMaoPropria=' + @CdMaoPropria +

'&nVlValorDeclarado=' + CAST(@nVlValorDeclarado AS VARCHAR(20)) +

'&sCdAvisoRecebimento=' + @CdAvisoRecebimento +

'&nCdServico=' + CAST(@nCdServico AS VARCHAR(20)) +

'&nVlDiametro=' + CAST(@nVlDiametro AS VARCHAR(20)) + '&StrRetorno=xml'

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

SET @xml = @resposta COLLATE SQL_Latin1_General_CP1251_CS_AS

SELECT

@xml.value('(/Servicos/cServico/Codigo)[1]', 'bigint') AS Codigo_Servico,

@xml.value('(/Servicos/cServico/Valor)[1]', 'varchar(20)') AS Valor_Frete,

@xml.value('(/Servicos/cServico/PrazoEntrega)[1]', 'int') AS Prazo_Entrega,

@xml.value('(/Servicos/cServico/ValorSemAdicionais)[1]', 'varchar(20)') AS Valor_Sem_Adicionais,

@xml.value('(/Servicos/cServico/ValorMaoPropria)[1]', 'varchar(20)') AS Valor_Entrega_Em_Maos,

@xml.value('(/Servicos/cServico/ValorAvisoRecebimento)[1]', 'varchar(20)') AS Valor_Aviso_Recebimento,

@xml.value('(/Servicos/cServico/EntregaDomiciliar)[1]', 'varchar(20)') AS Entrega_Domiciliar,

@xml.value('(/Servicos/cServico/EntregaDomiciliar)[1]', 'varchar(1)') AS Entrega_Domiciliar,

@xml.value('(/Servicos/cServico/EntregaSabado)[1]', 'varchar(1)') AS Entrega_Sabado,

@xml.value('(/Servicos/cServico/Erro)[1]', 'int') AS Codigo_Erro,

@xml.value('(/Servicos/cServico/MsgErro)[1]', 'varchar(500)') AS Mensagem_Erro

END

Exemplos de uso

Realizando um teste simples, preenchendo só os parâmetros obrigatórios

Realizando um teste mais completo, preenchendo todos os parâmetros da SP

Chamando a SP, mas informando um CEP inválido

And that's it, folks!
Até o próximo post!

thiago disse:

August 16, 2016 às 21:40

Olá Dirceu achei esse post otimo, realizei varios testes com webservice usando essa mesma lógica. A minha duvida fica com base em qual a vantagem ou desvantagem de desenvolver a comunicação do webservice via SQL ou via linguagem de programação como C# por exemplo? Aonde trabalho os desenvolvedores usam C# para a comunicação com diversos webservices e em alguns casos me parece ser até mais trabalhoso e oneroso atuar dessa maneira.

Responder
- Dirceu Resende disse:
  
  August 16, 2016 às 22:41
  
  Thiago,
  Boa noite e obrigado pela visita.
  
  No caso desse exemplo, o webservice dos correios é bem simples e responde a requisições simples HTTP. Para esse tipo de situação, realizar consultas é bem mais simples mesmo pelo SQL server do que pelo C#, embora eu tenha até feito um post de como criar uma SP no c# e executar pelo SQL server (Usando o CLR) para realizar requisições post e get que fica até mais fácil do que usando OLE Automation.
  
  Porém, quando você vai trabalhar com Webservices mais completos, que possuem WSDL, métodos de chamadas, retornos de tipos complexos, etc, isso não é possível de ser utilizado pelo SQL server puro. Nesses casos, você precisa de uma linguagem de programação para atuar nisso. Na empresa que eu trabalho, eu costumo criar a integração com o Webservice no visual studio, que já lê a URL do Webservice e gera as classes prontas em C# e encapsulo isso na minha biblioteca CLR, onde posso consultar o Webservice pelo SQL server e retornar como um XML ou mesmo já tratar o XML pelo próprio C# e retornar os dados como tabela.
  
  Provavelmente nesse fim de semana vou criar uma post sobre isso 🙂
  
  Responder

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

Como calcular o valor do frete e prazo de entrega utilizando WebService dos Correios no SQL Server

Conhecendo o WebService dos Correios

WebService Correios - Parâmetros de Entrada

WebService Correios - Parâmetros de Retorno

WebService Correios - Códigos de Serviço

Criando a Stored Procedure para consultar os dados

Exemplos de uso

You may also like...

2 Responses

Deixe uma respostaCancelar resposta

Conhecendo o WebService dos Correios

WebService Correios - Parâmetros de Entrada

WebService Correios - Parâmetros de Retorno

WebService Correios - Códigos de Serviço

Criando a Stored Procedure para consultar os dados

Exemplos de uso

You may also like...

SQL Server – Como fazer uma integração do banco de dados com o Telegram e enviar mensagens utilizando o CLR (C#)

Auditing in SQL Server (Server Audit)

SQL Server – Qual a diferença entre @@ERROR e ERROR_NUMBER() ?

2 Responses

Deixe uma respostaCancelar resposta