¡Hola, chicos!
En este artículo, compartiré con ustedes una solución que he estado buscando durante mucho tiempo, que es cómo enviar mensajes a contactos, grupos y listas de difusión de Whatsapp usando WebRequests. Ya había hecho algo similar usando otras herramientas, pero nunca Whatsapp, que no tiene API oficial y es completamente cerrado en este sentido.

Aunque existen herramientas de comunicación incluso mejores que Whatsapp, como el propio Telegram, 100 de cada 100 personas que conozco tienen una cuenta de Whatsapp y la mayoría no tiene una cuenta de Telegram, por lo que creo que este artículo podría terminar convirtiéndose en una utilidad pública para cualquiera que quiera crear cualquier automatización involucrando a Whatsapp.

Consulte mis artículos sobre otras herramientas de comunicación:

API de comunicación de Whatsapp

La protagonista de este artículo, la API NETiZap es la que posibilita una fácil integración entre la base de datos SQL Server y el comunicador de Whatsapp, ya que no cuenta con una API oficial y es completamente cerrada al respecto. Ya intenté realizar algunas integraciones directamente con Whatsapp y, después de algunas pruebas, me banearon durante 24 horas y me notificaron que la próxima vez mi número sería baneado de la plataforma para siempre. Después de eso, terminé dejando estas pruebas de lado... jajaja

Por eso usar una API es tan práctico y seguro: El número que realiza las llamadas es el número de API y no tu número personal/corporativo.

Para probar esta API, puedes utilizar la versión demo, que es gratuita, pero para implementar rutinas reales, debes adquirir un plan pago:

DepartamentoModalidadCantidad de envíosValor
Plan gratuitoGratis para fines de demostración, prueba o periódicos.CostumbreGratis
Plan PrepagoPaquete de mensajes + soporte100R$ 0,20 por mensaje + R$ 50,00 mensuales
Plan PospagoCuota Mensual + SoporteIlimitadoR$ 200,00
Plan Pospago para RevendedoresCuota Mensual + SoporteIlimitadoR$ 130,00 (Mínimo 5 líneas)

Normas:

  • Está prohibido el uso con fines de spam.
  • Normas supervisadas y aplicadas por WhatsApp

Dentro del paquete API, puede utilizar la aplicación ConsumersAPI.exe, que le permite probar todos los métodos API con los parámetros predeterminados:

También puedes probar usando Postman:

SQL Server - Cómo enviar mensajes a contactos, grupos y listas de difusión de WhatsApp 1
SQL Server - Cómo enviar mensajes a contactos, grupos y listas de difusión de WhatsApp 2
SQL Server - Cómo enviar mensajes a contactos, grupos y listas de difusión de WhatsApp 3
SQL Server - Cómo enviar mensajes a contactos, grupos y listas de difusión de WhatsApp 4

La documentación para esta API está disponible en este enlace aquí. Para descargar el paquete de desarrollador de API, haga clic aquí en este enlace.

Si estás interesado en contratar esta API para tu empresa, contacta por teléfono con Thiago, desarrollador de esta API +55 27 99802-2075. El número utilizado para enviar mensajes no es fijo y puede ser modificado/elegido por la empresa contratante.

Envío de mensajes a WhatsApp con OLE Automation

Haga clic aquí para ver el contenido
Una de las formas de realizar esta comunicación entre SQL Server y la API es utilizando el Automatización OLE, que crea instancias de objetos de Windows en SQL Server. Este método ya es bastante antiguo en SQL Server, conocido por algunas pérdidas de memoria y debe usarse como último recurso, ya que SQLCLR es más seguro y flexible que este método (y la acentuación funciona), pero también un poco más complicado de implementar.

Enviar mensajes privados a través de Whatsapp

Para enviar un mensaje a un contacto específico de Whatsapp, use el siguiente código:

-- ALTERAR CONFORME NECESSIDADE
DECLARE 
    @Destinatario VARCHAR(30) = 'telefone_destino', -- FORMATO: ddnumero Ex: 11988543306
    @Mensagem VARCHAR(MAX) = 'Teste da API',
    @Chave VARCHAR(100) = 'Voz5dWkr5EEhBAbYuwJ2',
    @Usuario VARCHAR(100) = 'user',
    @Senha VARCHAR(100) = 'api'


-- NÃO ALTERAR DAQUI PARA BAIXO
DECLARE
    @obj INT,
    @Url VARCHAR(255),
    @resposta VARCHAR(8000),
    @Parametros VARCHAR(4000),
    @Autorizacao VARCHAR(100),
    @source VARBINARY(MAX)


--------------------------------------------------------------------------------
-- 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



--------------------------------------------------------------------------------
-- Realizando a requisição
--------------------------------------------------------------------------------

SET @Url = 'http://api.meuaplicativo.vip:13005/services/message_send?line=5527981049976&destiny=55' + @Destinatario + '&reference&text=' + @Mensagem
SET @Parametros = 'App=NetiZap Consumers 1.0&AccessKey=' + @Chave

SET @source = CONVERT(VARBINARY(MAX), @Usuario + ':' + @Senha)
SET @Autorizacao = 'Basic ' + CAST('' AS XML).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')

EXEC sys.sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT
EXEC sys.sp_OAMethod @obj, 'open', NULL, 'POST', @Url, 'FALSE'
EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Authorization', @Autorizacao
EXEC sys.sp_OAMethod @obj, 'send', NULL, @Parametros

EXEC sys.sp_OAGetProperty @obj, 'responseText', @resposta OUT
EXEC sys.sp_OADestroy @obj
    
SELECT @resposta

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

IF (@Fl_Ole_Automation_Ativado = 0)
BEGIN

    EXECUTE sp_configure 'show advanced options', 0;
    RECONFIGURE WITH OVERRIDE;
    
    EXEC sp_configure 'Ole Automation Procedures', 0;
    RECONFIGURE WITH OVERRIDE;
    
END

Resultado:

Cómo llegar a tu celular:

Cómo recuperar la lista de grupos disponibles

Para enumerar los grupos disponibles en su cuenta, puede utilizar el siguiente script. Para enviar un mensaje al grupo, simplemente use el mismo script de envío de mensajes privados y reemplace el número de teléfono del destinatario con la identificación del grupo que se devuelve a continuación.

El tratamiento JSON se realizó con la función OPENJSON, disponible en SQL Server 2016. Si su versión es anterior a esa, puede reemplazarla con un simple SELECT en la variable e identificar manualmente la identificación del grupo deseado. Si quieres saber más sobre el manejo de cadenas JSON, echa un vistazo a mi artículo. SQL Server 2016: uso de compatibilidad con JSON nativo (JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON, ISJSON, JSON_MODIFY).

-- ALTERAR CONFORME NECESSIDADE
DECLARE 
    @Chave VARCHAR(100) = 'Voz5dWkr5EEhBAbYuwJ2',
    @Usuario VARCHAR(100) = 'user',
    @Senha VARCHAR(100) = 'api'


-- NÃO ALTERAR DAQUI PARA BAIXO
DECLARE
    @obj INT,
    @Url VARCHAR(255),
    @resposta VARCHAR(8000),
    @Parametros VARCHAR(4000),
    @Autorizacao VARCHAR(100),
    @source VARBINARY(MAX)


--------------------------------------------------------------------------------
-- 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



--------------------------------------------------------------------------------
-- Realizando a requisição
--------------------------------------------------------------------------------

SET @Url = 'http://api.meuaplicativo.vip:13005/services/group_search?line=5527981049976&AccessKey=' + @Chave

SET @source = CONVERT(VARBINARY(MAX), @Usuario + ':' + @Senha)
SET @Autorizacao = 'Basic ' + CAST('' AS XML).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')

EXEC sys.sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT
EXEC sys.sp_OAMethod @obj, 'open', NULL, 'GET', @Url, 'FALSE'
EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Authorization', @Autorizacao
EXEC sys.sp_OAMethod @obj, 'send', NULL, @Parametros

EXEC sys.sp_OAGetProperty @obj, 'responseText', @resposta OUT
EXEC sys.sp_OADestroy @obj
    

SELECT
    conteudo.id,
    conteudo.descricao
FROM 
    OPENJSON(@resposta, '$')
    WITH (
        [root] NVARCHAR(MAX) AS JSON
    ) AS elemento
    CROSS APPLY OPENJSON(elemento.[root]) WITH(
        id VARCHAR(200),
        descricao VARCHAR(500)
    ) AS conteudo


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

IF (@Fl_Ole_Automation_Ativado = 0)
BEGIN

    EXECUTE sp_configure 'show advanced options', 0;
    RECONFIGURE WITH OVERRIDE;
    
    EXEC sp_configure 'Ole Automation Procedures', 0;
    RECONFIGURE WITH OVERRIDE;
    
END

Resultado:

Cómo enviar mensajes a un grupo de Whatsapp

Ahora que he demostrado cómo enviar un mensaje privado y cómo enumerar grupos, ¡combinemos los dos y enviemos mensajes a un grupo!

-- ALTERAR CONFORME NECESSIDADE
DECLARE
    @Destino VARCHAR(50),
    @Mensagem VARCHAR(8000) = 'Ola! \nEsta e uma mensagem de *teste* para o ~grupo~.',
    @Chave VARCHAR(100) = 'Voz5dWkr5EEhBAbYuwJ2',
    @Usuario VARCHAR(100) = 'user',
    @Senha VARCHAR(100) = 'api'


-- NÃO ALTERAR DAQUI PARA BAIXO
DECLARE
    @obj INT,
    @Url_Mensagem NVARCHAR(4000),
    @Url_Grupo VARCHAR(4000) = 'http://api.meuaplicativo.vip:13005/services/group_search?line=5527981049976&AccessKey=' + @Chave,
    @resposta VARCHAR(8000),
    @Parametros VARCHAR(4000) = 'App=NetiZap Consumers 1.0&AccessKey=' + @Chave,
    @Autorizacao VARCHAR(100),
    @source VARBINARY(MAX)


--------------------------------------------------------------------------------
-- 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



--------------------------------------------------------------------------------
-- Realizando a requisição
--------------------------------------------------------------------------------

SET @source = CONVERT(VARBINARY(MAX), @Usuario + ':' + @Senha)
SET @Autorizacao = 'Basic ' + CAST('' AS XML).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')

EXEC sys.sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT
EXEC sys.sp_OAMethod @obj, 'open', NULL, 'GET', @Url_Grupo, 'FALSE'
EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Authorization', @Autorizacao
EXEC sys.sp_OAMethod @obj, 'send', NULL, NULL

EXEC sys.sp_OAGetProperty @obj, 'responseText', @resposta OUT
EXEC sys.sp_OADestroy @obj
    

-- Gravo a lista dos grupos numa tabela temporária
IF (OBJECT_ID('tempdb..#Grupos') IS NOT NULL) DROP TABLE #Grupos
SELECT
    conteudo.id,
    conteudo.descricao
INTO
    #Grupos
FROM 
    OPENJSON(@resposta, '$')
    WITH (
        [root] NVARCHAR(MAX) AS JSON
    ) AS elemento
    CROSS APPLY OPENJSON(elemento.[root]) WITH(
        id VARCHAR(200),
        descricao VARCHAR(500)
    ) AS conteudo


SET @obj = NULL
SET @resposta = NULL
SET @Destino = (SELECT TOP(1) id FROM #Grupos WHERE descricao = 'Teste API')
SET @Url_Mensagem = 'http://api.meuaplicativo.vip:13005/services/message_send?line=5527981049976&destiny=' + @Destino + '&text=' + @Mensagem


EXEC sys.sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT
EXEC sys.sp_OAMethod @obj, 'open', NULL, 'POST', @Url_Mensagem, 'FALSE'
EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded; charset=utf-8'
EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Charset', 'UTF-8'
EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Authorization', @Autorizacao
EXEC sys.sp_OAMethod @obj, 'send', NULL, @Parametros

EXEC sys.sp_OAGetProperty @obj, 'responseText', @resposta OUT
EXEC sys.sp_OADestroy @obj


SELECT @resposta


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

IF (@Fl_Ole_Automation_Ativado = 0)
BEGIN

    EXECUTE sp_configure 'show advanced options', 0;
    RECONFIGURE WITH OVERRIDE;
    
    EXEC sp_configure 'Ole Automation Procedures', 0;
    RECONFIGURE WITH OVERRIDE;
    
END

Cómo llegar a tu celular:

Cómo recuperar la lista de transmisiones disponibles para su cuenta

Casi idéntico al listado de grupos de cuentas, el siguiente script le permite enumerar las listas de transmisión disponibles para enviar a través de la API:

-- ALTERAR CONFORME NECESSIDADE
DECLARE 
    @Chave VARCHAR(100) = 'Voz5dWkr5EEhBAbYuwJ2',
    @Usuario VARCHAR(100) = 'user',
    @Senha VARCHAR(100) = 'api'


-- NÃO ALTERAR DAQUI PARA BAIXO
DECLARE
    @obj INT,
    @Url VARCHAR(255),
    @resposta VARCHAR(8000),
    @Parametros VARCHAR(4000),
    @Autorizacao VARCHAR(100),
    @source VARBINARY(MAX)


--------------------------------------------------------------------------------
-- 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



--------------------------------------------------------------------------------
-- Realizando a requisição
--------------------------------------------------------------------------------

SET @Url = 'http://api.meuaplicativo.vip:13005/services/broadcast_search?line=5527981049976&AccessKey=' + @Chave

SET @source = CONVERT(VARBINARY(MAX), @Usuario + ':' + @Senha)
SET @Autorizacao = 'Basic ' + CAST('' AS XML).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')

EXEC sys.sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT
EXEC sys.sp_OAMethod @obj, 'open', NULL, 'GET', @Url, 'FALSE'
EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Authorization', @Autorizacao
EXEC sys.sp_OAMethod @obj, 'send', NULL, @Parametros

EXEC sys.sp_OAGetProperty @obj, 'responseText', @resposta OUT
EXEC sys.sp_OADestroy @obj
    

SELECT
    conteudo.id,
    conteudo.descricao
FROM 
    OPENJSON(@resposta, '$')
    WITH (
        [root] NVARCHAR(MAX) AS JSON
    ) AS elemento
    CROSS APPLY OPENJSON(elemento.[root]) WITH(
        id VARCHAR(200),
        descricao VARCHAR(500)
    ) AS conteudo


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

IF (@Fl_Ole_Automation_Ativado = 0)
BEGIN

    EXECUTE sp_configure 'show advanced options', 0;
    RECONFIGURE WITH OVERRIDE;
    
    EXEC sp_configure 'Ole Automation Procedures', 0;
    RECONFIGURE WITH OVERRIDE;
    
END

Resultado:

Cómo crear bibliotecas SQLCLR (solo si usa SQLCLR)

Haga clic aquí para ver el contenido
En este artículo, demostraré dos técnicas para realizar solicitudes HTTP a través de SQL Server: SQLCLR y OLE Automation. Si desea utilizar SQLCLR, este tema sirve para instalar los objetos necesarios para poder seguir los ejemplos de este artículo. Si elige utilizar OLE Automation, puede omitir este tema.

La forma más sencilla y robusta de realizar solicitudes HTTP a través de SQL Server es, sin duda, utilizando el SQLCLR (haga clic aquí si ni siquiera sabes qué es SQLCLR).

Cómo crear stpWs_Requisicao en su entorno (SQL Server 2012 a 2016):

Si está utilizando las versiones 2012, 2014 o 2016 de SQL Server, puede utilizar el siguiente script para crear el ensamblado (DLL) en la base de datos deseada y el procedimiento almacenado “stpWs_Requisicao”, que se utiliza para realizar la solicitud HTTP API.

USE [Base_Teste]
GO

-- Apago os objetos desse post, caso já tenham sido criados
IF (OBJECT_ID('dbo.stpWs_Requisicao') IS NOT NULL) DROP PROCEDURE dbo.stpWs_Requisicao;
IF (EXISTS(SELECT NULL FROM sys.assemblies WHERE [name] = @clrName)) DROP ASSEMBLY [SQLCLR_Requisicao_Web]

-- Crio o assembly no banco
CREATE ASSEMBLY [SQLCLR_Requisicao_Web]
    AUTHORIZATION [dbo]
    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300EB85E45D0000000000000000E00022200B013000001200000006000000000000AA300000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000583000004F00000000400000D802000000000000000000000000000000000000006000000C000000202F00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B0100000002000000012000000020000000000000000000000000000200000602E72737263000000D8020000004000000004000000140000000000000000000000000000400000402E72656C6F6300000C00000000600000000200000018000000000000000000000000000040000042000000000000000000000000000000008C30000000000000480000000200050034240000EC0A000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B3005003003000001000011000F02280600000A2D090F02280700000A2B0572010000700A0F01280600000A2D090F01280700000A2B0572030000700B0F00280700000A0C7E0800000A0D0F03280600000A2D090F03280700000A2B05720B00007013040F04280600000A2D090F04280700000A2B05720100007013050F05280600000A2D090F05280700000A2B05720100007013060F07280600000A2D090F07280700000A2B05720100007013070F06280900000A2D090F06280A00000A2B011613080F08280B00000A2D090F08280C00000A2B021F1E13091104280D00000A130A110A2C07720B0000701304000008280E00000A740A000001130B110B076F0F00000A00110B110920E80300005A6F1000000A000872170000706F1100000A16FE0416FE01130C110C2C390017281200000A0020F00F0000281300000A007E02000004252D17267E01000004FE0605000006731400000A258002000004281500000A00001106280D00000A16FE01130D110D2C0C00110B11066F1600000A00001105280D00000A16FE01130E110E2C0C00110B11056F1700000A00001107280D00000A16FE01130F110F2C77001107178D1800000125161F7C9D6F1800000A131011108E6913111613122B4A00111011129A178D1400000125167229000070A2166F1900000A13131113169A6F1A00000A13141113179A6F1A00000A1315110B6F1B00000A111411156F1C00000A000011121758131211121111FE04131611162DAA001108131711172C02000007722F000070281D00000A2D0D077239000070281D00000A2C0B066F1E00000A16FE022B0116131811182C5100061319281F00000A11196F2000000A131A110B111A8E696A6F2100000A00110B6F2200000A131B00111B111A16111A8E696F2300000A00111B6F2400000A0000DE0D111B2C08111B6F2500000A00DC00110B6F2600000A740C000001131C00111C6F2700000A131D00111D14FE03131E111E2C2B00111D1104282800000A732900000A131F00111F6F2A00000A0D00DE0D111F2C08111F6F2500000A00DC0000DE0D111D2C08111D6F2500000A00DC00DE0D111C2C08111C6F2500000A00DC00DE1013200011206F2B00000A732C00000A7A0E0909282D00000A81070000012A417C000002000000770200001B000000920200000D0000000000000002000000D50200000C000000E10200000D0000000000000002000000B80200003A000000F20200000D0000000000000002000000AE02000054000000020300000D0000000000000000000000E30000002F02000012030000100000000E0000012202282E00000A002A2E730400000680010000042A00000013300100070000000200001100170A2B00062A0042534A4201000100000000000C00000076342E302E33303331390000000005006C00000070030000237E0000DC0300007805000023537472696E67730000000054090000440000002355530098090000100000002347554944000000A80900004401000023426C6F620000000000000002000001571502000902000000FA01330016000001000000200000000300000002000000050000000E0000002E0000000500000002000000010000000300000001000000000096020100000000000600B101EE030600D101EE0306008701DB030F000E0400000600C204FA020A009B01B3030A0023021D040A0006031D040A0023001D040E00FB04C9040600F30256000E002501C90406008703560006004E03FA0206006C01EE030E004A02420506004F0143040E00110343040E00B204420506002602FA020E00FF04C9040E009F03C9040E00F100C90406008203FA0206007A04FA020E002F03C9040E001B03A50006001A022A050600E500FA020E002901C90406009403560006004303FA02000000004D0000000000010001000100100032040000150001000100032110009D00000015000100030036003500DC0016000100E00050200000000096006703E40001000824000000008618CE0306000B001124000000009118D403FD000B000824000000008618CE0306000B0020240000000083000A0001010B0000000100D002000002007803000003008D0400000400580300000500E604000006000601000007006405000008009B04000009000A0502000A006000000001003900000002003E000000030043000000040048000900CE0301001100CE0306001900CE030A003100CE0306007900CE0306003900B0023D003900EF014100A1005E0545004100B0023D004100EF013D004900B0023D004900EF014800A10056054C00A9005F015100A900CE005700A9001E050100A1000F025C00B100F9016100B100BB0266008100CE036C00B1006E027200A900150157005100F0045700A100E0047800A100E0047F00A10001034100A900A6048800D900A1008D00A10036059300A1002D024800E1002C009900E10071049E00A9003802A400A900E902A90059006601AE00590041010600E90047010600A9003501B600F100D702A900E1001702BB006900CE03C100F900C40041007100D90041000101CE0357003900D404C9002900CE030600200023003D012E000B000C012E00130015012E001B00340163002B003D011000CF000480000000000000000000000000000000007E000000040000000000000000000000D300940000000000040000000000000000000000D300720000000000040000000000000000000000D300FA0200000000030002000000003C3E395F5F305F30003C73747057735F5265717569736963616F3E625F5F305F300053716C496E743332006765745F55544638003C3E39003C70303E003C70313E003C70323E003C70333E003C4D6F64756C653E0053797374656D2E494F0044735F5265746F726E6F5F4F55545055540053797374656D2E446174610053514C434C525F5265717569736963616F5F576562006D73636F726C6962003C3E63004164640053797374656D2E436F6C6C656374696F6E732E5370656369616C697A65640052656164546F456E64007365745F4D6574686F64006765745F4D6573736167650049446973706F7361626C6500536563757269747950726F746F636F6C547970650044735F436F6E74656E7454797065007365745F436F6E74656E74547970650048747470576562526573706F6E736500476574526573706F6E736500436C6F736500446973706F7365005835303943657274696669636174650043726561746500577269746500436F6D70696C657247656E6572617465644174747269627574650044656275676761626C654174747269627574650053716C50726F63656475726541747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C7565007365745F457870656374313030436F6E74696E756500496E6465784F6600476574456E636F64696E670053716C537472696E67006765745F4C656E677468007365745F436F6E74656E744C656E6774680052656D6F7465436572746966696361746556616C69646174696F6E43616C6C6261636B007365745F536572766572436572746966696361746556616C69646174696F6E43616C6C6261636B0053514C434C525F5265717569736963616F5F5765622E646C6C006765745F49734E756C6C007365745F536563757269747950726F746F636F6C0044735F55726C00476574526573706F6E736553747265616D004765745265717565737453747265616D0053797374656D005472696D0053716C426F6F6C65616E0058353039436861696E004E616D6556616C7565436F6C6C656374696F6E00576562486561646572436F6C6C656374696F6E004170706C69636174696F6E457863657074696F6E0044735F436F64696669636163616F0073747057735F5265717569736963616F0044735F4D65746F646F00436861720053747265616D52656164657200546578745265616465720053657276696365506F696E744D616E61676572004D6963726F736F66742E53716C5365727665722E536572766572002E63746F72002E6363746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730053746F72656450726F636564757265730053797374656D2E53656375726974792E43727970746F6772617068792E5835303943657274696669636174657300476574427974657300537472696E6753706C69744F7074696F6E730044735F506172616D6574726F730044735F48656164657273006765745F486561646572730053736C506F6C6963794572726F7273004F626A6563740053797374656D2E4E6574006F705F496D706C696369740053706C69740044735F416363657074007365745F4163636570740048747470576562526571756573740051745F536567756E646F735F54696D656F7574007365745F54696D656F75740053797374656D2E54657874006F705F457175616C6974790053797374656D2E4E65742E53656375726974790049734E756C6C4F72456D70747900466C5F417574656E746963615F50726F7879000000010007470045005400000B5500540046002D0038000111680074007400700073003A002F002F0000053A003A00000950004F005300540000075000550054000000000091D3434B91A6944B9944F25BAF4B39B0000420010108032000010520010111112C07210E0E0E0E0E0E0E0E0208021229020202021D0E08081D0E0E0E0202020E1D05122D1231122D0212351239032000020320000E02060E03200008040001020E05000112550E042001010E042001080E040001010205000101115D052002011C180500010112410620011D0E1D030820021D0E1D0E11650420001269052002010E0E050002020E0E04000012710520011D050E042001010A042000122D072003011D050808042000127905000112710E07200201122D1271050001111D0E0307010208B77A5C561934E0890306120C0306124118000A01111D111D111D111D111D111D1121111D112510111D030000010A2004021C12451249114D0801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000701000000000401000000000000000000EB85E45D00000000020000001C0100003C2F00003C110000525344538863FD7F0DBEED47BBE2FD478149B89501000000433A5C55736572735C6469726365755C736F757263655C7265706F735C53514C434C525F5265717569736963616F5F5765625C53514C434C525F5265717569736963616F5F5765625C6F626A5C44656275675C53514C434C525F5265717569736963616F5F5765622E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000008030000000000000000000009A3000000020000000000000000000000000000000000000000000008C300000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000007C02000000000000000000007C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004DC010000010053007400720069006E006700460069006C00650049006E0066006F000000B801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000054001A00010049006E007400650072006E0061006C004E0061006D0065000000530051004C0043004C0052005F005200650071007500690073006900630061006F005F005700650062002E0064006C006C0000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000005C001A0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0043004C0052005F005200650071007500690073006900630061006F005F005700650062002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000AC3000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
    WITH PERMISSION_SET = EXTERNAL_ACCESS;


-- Crio a Stored Procedure "stpWs_Requisicao"
CREATE PROCEDURE [dbo].[stpWs_Requisicao] @Ds_Url NVARCHAR (MAX), @Ds_Metodo NVARCHAR (MAX), @Ds_Parametros NVARCHAR (MAX), @Ds_Codificacao NVARCHAR (MAX), @Ds_Accept NVARCHAR (MAX), @Ds_ContentType NVARCHAR (MAX), @Fl_Autentica_Proxy BIT, @Ds_Headers NVARCHAR (MAX), @Qt_Segundos_Timeout INT, @Ds_Retorno_OUTPUT NVARCHAR (MAX) OUTPUT
AS EXTERNAL NAME [SQLCLR_Requisicao_Web].[StoredProcedures].[stpWs_Requisicao]

Cómo crear stpWs_Requisicao en su entorno (SQL Server 2017+):

Si está utilizando una versión mayor o igual a 2017 de SQL Server, puede utilizar el siguiente script para crear el ensamblado (DLL) en la base de datos deseada y el procedimiento almacenado “stpWs_Requisicao”, que se utiliza para realizar la solicitud HTTP API. Este script es diferente al anterior, pues a partir de la versión 2017, SQL Server ahora requiere la firma de ensamblados incluso en Modo Seguro, debido a cambios en la seguridad de .NET Framework.

USE [Base_Teste]
GO

-- Habilito o modo TRUSTWORTHY para conseguir criar o assembly no modo EXTERNAL_ACCESS
ALTER DATABASE Base_Teste SET TRUSTWORTHY ON
GO

-- Gero a hash para marcar o assembly como confiável
DECLARE 
    @clrName nvarchar(4000) = 'SQLCLR_Requisicao_Web',
    @asmBin varbinary(max) = 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300EB85E45D0000000000000000E00022200B013000001200000006000000000000AA300000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000583000004F00000000400000D802000000000000000000000000000000000000006000000C000000202F00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B0100000002000000012000000020000000000000000000000000000200000602E72737263000000D8020000004000000004000000140000000000000000000000000000400000402E72656C6F6300000C00000000600000000200000018000000000000000000000000000040000042000000000000000000000000000000008C30000000000000480000000200050034240000EC0A000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B3005003003000001000011000F02280600000A2D090F02280700000A2B0572010000700A0F01280600000A2D090F01280700000A2B0572030000700B0F00280700000A0C7E0800000A0D0F03280600000A2D090F03280700000A2B05720B00007013040F04280600000A2D090F04280700000A2B05720100007013050F05280600000A2D090F05280700000A2B05720100007013060F07280600000A2D090F07280700000A2B05720100007013070F06280900000A2D090F06280A00000A2B011613080F08280B00000A2D090F08280C00000A2B021F1E13091104280D00000A130A110A2C07720B0000701304000008280E00000A740A000001130B110B076F0F00000A00110B110920E80300005A6F1000000A000872170000706F1100000A16FE0416FE01130C110C2C390017281200000A0020F00F0000281300000A007E02000004252D17267E01000004FE0605000006731400000A258002000004281500000A00001106280D00000A16FE01130D110D2C0C00110B11066F1600000A00001105280D00000A16FE01130E110E2C0C00110B11056F1700000A00001107280D00000A16FE01130F110F2C77001107178D1800000125161F7C9D6F1800000A131011108E6913111613122B4A00111011129A178D1400000125167229000070A2166F1900000A13131113169A6F1A00000A13141113179A6F1A00000A1315110B6F1B00000A111411156F1C00000A000011121758131211121111FE04131611162DAA001108131711172C02000007722F000070281D00000A2D0D077239000070281D00000A2C0B066F1E00000A16FE022B0116131811182C5100061319281F00000A11196F2000000A131A110B111A8E696A6F2100000A00110B6F2200000A131B00111B111A16111A8E696F2300000A00111B6F2400000A0000DE0D111B2C08111B6F2500000A00DC00110B6F2600000A740C000001131C00111C6F2700000A131D00111D14FE03131E111E2C2B00111D1104282800000A732900000A131F00111F6F2A00000A0D00DE0D111F2C08111F6F2500000A00DC0000DE0D111D2C08111D6F2500000A00DC00DE0D111C2C08111C6F2500000A00DC00DE1013200011206F2B00000A732C00000A7A0E0909282D00000A81070000012A417C000002000000770200001B000000920200000D0000000000000002000000D50200000C000000E10200000D0000000000000002000000B80200003A000000F20200000D0000000000000002000000AE02000054000000020300000D0000000000000000000000E30000002F02000012030000100000000E0000012202282E00000A002A2E730400000680010000042A00000013300100070000000200001100170A2B00062A0042534A4201000100000000000C00000076342E302E33303331390000000005006C00000070030000237E0000DC0300007805000023537472696E67730000000054090000440000002355530098090000100000002347554944000000A80900004401000023426C6F620000000000000002000001571502000902000000FA01330016000001000000200000000300000002000000050000000E0000002E0000000500000002000000010000000300000001000000000096020100000000000600B101EE030600D101EE0306008701DB030F000E0400000600C204FA020A009B01B3030A0023021D040A0006031D040A0023001D040E00FB04C9040600F30256000E002501C90406008703560006004E03FA0206006C01EE030E004A02420506004F0143040E00110343040E00B204420506002602FA020E00FF04C9040E009F03C9040E00F100C90406008203FA0206007A04FA020E002F03C9040E001B03A50006001A022A050600E500FA020E002901C90406009403560006004303FA02000000004D0000000000010001000100100032040000150001000100032110009D00000015000100030036003500DC0016000100E00050200000000096006703E40001000824000000008618CE0306000B001124000000009118D403FD000B000824000000008618CE0306000B0020240000000083000A0001010B0000000100D002000002007803000003008D0400000400580300000500E604000006000601000007006405000008009B04000009000A0502000A006000000001003900000002003E000000030043000000040048000900CE0301001100CE0306001900CE030A003100CE0306007900CE0306003900B0023D003900EF014100A1005E0545004100B0023D004100EF013D004900B0023D004900EF014800A10056054C00A9005F015100A900CE005700A9001E050100A1000F025C00B100F9016100B100BB0266008100CE036C00B1006E027200A900150157005100F0045700A100E0047800A100E0047F00A10001034100A900A6048800D900A1008D00A10036059300A1002D024800E1002C009900E10071049E00A9003802A400A900E902A90059006601AE00590041010600E90047010600A9003501B600F100D702A900E1001702BB006900CE03C100F900C40041007100D90041000101CE0357003900D404C9002900CE030600200023003D012E000B000C012E00130015012E001B00340163002B003D011000CF000480000000000000000000000000000000007E000000040000000000000000000000D300940000000000040000000000000000000000D300720000000000040000000000000000000000D300FA0200000000030002000000003C3E395F5F305F30003C73747057735F5265717569736963616F3E625F5F305F300053716C496E743332006765745F55544638003C3E39003C70303E003C70313E003C70323E003C70333E003C4D6F64756C653E0053797374656D2E494F0044735F5265746F726E6F5F4F55545055540053797374656D2E446174610053514C434C525F5265717569736963616F5F576562006D73636F726C6962003C3E63004164640053797374656D2E436F6C6C656374696F6E732E5370656369616C697A65640052656164546F456E64007365745F4D6574686F64006765745F4D6573736167650049446973706F7361626C6500536563757269747950726F746F636F6C547970650044735F436F6E74656E7454797065007365745F436F6E74656E74547970650048747470576562526573706F6E736500476574526573706F6E736500436C6F736500446973706F7365005835303943657274696669636174650043726561746500577269746500436F6D70696C657247656E6572617465644174747269627574650044656275676761626C654174747269627574650053716C50726F63656475726541747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C7565007365745F457870656374313030436F6E74696E756500496E6465784F6600476574456E636F64696E670053716C537472696E67006765745F4C656E677468007365745F436F6E74656E744C656E6774680052656D6F7465436572746966696361746556616C69646174696F6E43616C6C6261636B007365745F536572766572436572746966696361746556616C69646174696F6E43616C6C6261636B0053514C434C525F5265717569736963616F5F5765622E646C6C006765745F49734E756C6C007365745F536563757269747950726F746F636F6C0044735F55726C00476574526573706F6E736553747265616D004765745265717565737453747265616D0053797374656D005472696D0053716C426F6F6C65616E0058353039436861696E004E616D6556616C7565436F6C6C656374696F6E00576562486561646572436F6C6C656374696F6E004170706C69636174696F6E457863657074696F6E0044735F436F64696669636163616F0073747057735F5265717569736963616F0044735F4D65746F646F00436861720053747265616D52656164657200546578745265616465720053657276696365506F696E744D616E61676572004D6963726F736F66742E53716C5365727665722E536572766572002E63746F72002E6363746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730053746F72656450726F636564757265730053797374656D2E53656375726974792E43727970746F6772617068792E5835303943657274696669636174657300476574427974657300537472696E6753706C69744F7074696F6E730044735F506172616D6574726F730044735F48656164657273006765745F486561646572730053736C506F6C6963794572726F7273004F626A6563740053797374656D2E4E6574006F705F496D706C696369740053706C69740044735F416363657074007365745F4163636570740048747470576562526571756573740051745F536567756E646F735F54696D656F7574007365745F54696D656F75740053797374656D2E54657874006F705F457175616C6974790053797374656D2E4E65742E53656375726974790049734E756C6C4F72456D70747900466C5F417574656E746963615F50726F7879000000010007470045005400000B5500540046002D0038000111680074007400700073003A002F002F0000053A003A00000950004F005300540000075000550054000000000091D3434B91A6944B9944F25BAF4B39B0000420010108032000010520010111112C07210E0E0E0E0E0E0E0E0208021229020202021D0E08081D0E0E0E0202020E1D05122D1231122D0212351239032000020320000E02060E03200008040001020E05000112550E042001010E042001080E040001010205000101115D052002011C180500010112410620011D0E1D030820021D0E1D0E11650420001269052002010E0E050002020E0E04000012710520011D050E042001010A042000122D072003011D050808042000127905000112710E07200201122D1271050001111D0E0307010208B77A5C561934E0890306120C0306124118000A01111D111D111D111D111D111D1121111D112510111D030000010A2004021C12451249114D0801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000701000000000401000000000000000000EB85E45D00000000020000001C0100003C2F00003C110000525344538863FD7F0DBEED47BBE2FD478149B89501000000433A5C55736572735C6469726365755C736F757263655C7265706F735C53514C434C525F5265717569736963616F5F5765625C53514C434C525F5265717569736963616F5F5765625C6F626A5C44656275675C53514C434C525F5265717569736963616F5F5765622E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000008030000000000000000000009A3000000020000000000000000000000000000000000000000000008C300000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000007C02000000000000000000007C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004DC010000010053007400720069006E006700460069006C00650049006E0066006F000000B801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000054001A00010049006E007400650072006E0061006C004E0061006D0065000000530051004C0043004C0052005F005200650071007500690073006900630061006F005F005700650062002E0064006C006C0000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000005C001A0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0043004C0052005F005200650071007500690073006900630061006F005F005700650062002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000AC3000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,
    @hash varbinary(64);

SET @hash = HASHBYTES('SHA2_512', @asmBin);


-- Apago os objetos desse post, caso já tenham sido criados
IF (OBJECT_ID('dbo.stpWs_Requisicao') IS NOT NULL) DROP PROCEDURE dbo.stpWs_Requisicao;
IF (EXISTS(SELECT NULL FROM sys.trusted_assemblies WHERE [description] = @clrName)) EXEC sys.sp_drop_trusted_assembly @hash = @hash;
IF (EXISTS(SELECT NULL FROM sys.assemblies WHERE [name] = @clrName)) DROP ASSEMBLY [SQLCLR_Requisicao_Web]

-- Crio o assembly no banco
CREATE ASSEMBLY [SQLCLR_Requisicao_Web]
    AUTHORIZATION [dbo]
    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300EB85E45D0000000000000000E00022200B013000001200000006000000000000AA300000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000583000004F00000000400000D802000000000000000000000000000000000000006000000C000000202F00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B0100000002000000012000000020000000000000000000000000000200000602E72737263000000D8020000004000000004000000140000000000000000000000000000400000402E72656C6F6300000C00000000600000000200000018000000000000000000000000000040000042000000000000000000000000000000008C30000000000000480000000200050034240000EC0A000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B3005003003000001000011000F02280600000A2D090F02280700000A2B0572010000700A0F01280600000A2D090F01280700000A2B0572030000700B0F00280700000A0C7E0800000A0D0F03280600000A2D090F03280700000A2B05720B00007013040F04280600000A2D090F04280700000A2B05720100007013050F05280600000A2D090F05280700000A2B05720100007013060F07280600000A2D090F07280700000A2B05720100007013070F06280900000A2D090F06280A00000A2B011613080F08280B00000A2D090F08280C00000A2B021F1E13091104280D00000A130A110A2C07720B0000701304000008280E00000A740A000001130B110B076F0F00000A00110B110920E80300005A6F1000000A000872170000706F1100000A16FE0416FE01130C110C2C390017281200000A0020F00F0000281300000A007E02000004252D17267E01000004FE0605000006731400000A258002000004281500000A00001106280D00000A16FE01130D110D2C0C00110B11066F1600000A00001105280D00000A16FE01130E110E2C0C00110B11056F1700000A00001107280D00000A16FE01130F110F2C77001107178D1800000125161F7C9D6F1800000A131011108E6913111613122B4A00111011129A178D1400000125167229000070A2166F1900000A13131113169A6F1A00000A13141113179A6F1A00000A1315110B6F1B00000A111411156F1C00000A000011121758131211121111FE04131611162DAA001108131711172C02000007722F000070281D00000A2D0D077239000070281D00000A2C0B066F1E00000A16FE022B0116131811182C5100061319281F00000A11196F2000000A131A110B111A8E696A6F2100000A00110B6F2200000A131B00111B111A16111A8E696F2300000A00111B6F2400000A0000DE0D111B2C08111B6F2500000A00DC00110B6F2600000A740C000001131C00111C6F2700000A131D00111D14FE03131E111E2C2B00111D1104282800000A732900000A131F00111F6F2A00000A0D00DE0D111F2C08111F6F2500000A00DC0000DE0D111D2C08111D6F2500000A00DC00DE0D111C2C08111C6F2500000A00DC00DE1013200011206F2B00000A732C00000A7A0E0909282D00000A81070000012A417C000002000000770200001B000000920200000D0000000000000002000000D50200000C000000E10200000D0000000000000002000000B80200003A000000F20200000D0000000000000002000000AE02000054000000020300000D0000000000000000000000E30000002F02000012030000100000000E0000012202282E00000A002A2E730400000680010000042A00000013300100070000000200001100170A2B00062A0042534A4201000100000000000C00000076342E302E33303331390000000005006C00000070030000237E0000DC0300007805000023537472696E67730000000054090000440000002355530098090000100000002347554944000000A80900004401000023426C6F620000000000000002000001571502000902000000FA01330016000001000000200000000300000002000000050000000E0000002E0000000500000002000000010000000300000001000000000096020100000000000600B101EE030600D101EE0306008701DB030F000E0400000600C204FA020A009B01B3030A0023021D040A0006031D040A0023001D040E00FB04C9040600F30256000E002501C90406008703560006004E03FA0206006C01EE030E004A02420506004F0143040E00110343040E00B204420506002602FA020E00FF04C9040E009F03C9040E00F100C90406008203FA0206007A04FA020E002F03C9040E001B03A50006001A022A050600E500FA020E002901C90406009403560006004303FA02000000004D0000000000010001000100100032040000150001000100032110009D00000015000100030036003500DC0016000100E00050200000000096006703E40001000824000000008618CE0306000B001124000000009118D403FD000B000824000000008618CE0306000B0020240000000083000A0001010B0000000100D002000002007803000003008D0400000400580300000500E604000006000601000007006405000008009B04000009000A0502000A006000000001003900000002003E000000030043000000040048000900CE0301001100CE0306001900CE030A003100CE0306007900CE0306003900B0023D003900EF014100A1005E0545004100B0023D004100EF013D004900B0023D004900EF014800A10056054C00A9005F015100A900CE005700A9001E050100A1000F025C00B100F9016100B100BB0266008100CE036C00B1006E027200A900150157005100F0045700A100E0047800A100E0047F00A10001034100A900A6048800D900A1008D00A10036059300A1002D024800E1002C009900E10071049E00A9003802A400A900E902A90059006601AE00590041010600E90047010600A9003501B600F100D702A900E1001702BB006900CE03C100F900C40041007100D90041000101CE0357003900D404C9002900CE030600200023003D012E000B000C012E00130015012E001B00340163002B003D011000CF000480000000000000000000000000000000007E000000040000000000000000000000D300940000000000040000000000000000000000D300720000000000040000000000000000000000D300FA0200000000030002000000003C3E395F5F305F30003C73747057735F5265717569736963616F3E625F5F305F300053716C496E743332006765745F55544638003C3E39003C70303E003C70313E003C70323E003C70333E003C4D6F64756C653E0053797374656D2E494F0044735F5265746F726E6F5F4F55545055540053797374656D2E446174610053514C434C525F5265717569736963616F5F576562006D73636F726C6962003C3E63004164640053797374656D2E436F6C6C656374696F6E732E5370656369616C697A65640052656164546F456E64007365745F4D6574686F64006765745F4D6573736167650049446973706F7361626C6500536563757269747950726F746F636F6C547970650044735F436F6E74656E7454797065007365745F436F6E74656E74547970650048747470576562526573706F6E736500476574526573706F6E736500436C6F736500446973706F7365005835303943657274696669636174650043726561746500577269746500436F6D70696C657247656E6572617465644174747269627574650044656275676761626C654174747269627574650053716C50726F63656475726541747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C7565007365745F457870656374313030436F6E74696E756500496E6465784F6600476574456E636F64696E670053716C537472696E67006765745F4C656E677468007365745F436F6E74656E744C656E6774680052656D6F7465436572746966696361746556616C69646174696F6E43616C6C6261636B007365745F536572766572436572746966696361746556616C69646174696F6E43616C6C6261636B0053514C434C525F5265717569736963616F5F5765622E646C6C006765745F49734E756C6C007365745F536563757269747950726F746F636F6C0044735F55726C00476574526573706F6E736553747265616D004765745265717565737453747265616D0053797374656D005472696D0053716C426F6F6C65616E0058353039436861696E004E616D6556616C7565436F6C6C656374696F6E00576562486561646572436F6C6C656374696F6E004170706C69636174696F6E457863657074696F6E0044735F436F64696669636163616F0073747057735F5265717569736963616F0044735F4D65746F646F00436861720053747265616D52656164657200546578745265616465720053657276696365506F696E744D616E61676572004D6963726F736F66742E53716C5365727665722E536572766572002E63746F72002E6363746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730053746F72656450726F636564757265730053797374656D2E53656375726974792E43727970746F6772617068792E5835303943657274696669636174657300476574427974657300537472696E6753706C69744F7074696F6E730044735F506172616D6574726F730044735F48656164657273006765745F486561646572730053736C506F6C6963794572726F7273004F626A6563740053797374656D2E4E6574006F705F496D706C696369740053706C69740044735F416363657074007365745F4163636570740048747470576562526571756573740051745F536567756E646F735F54696D656F7574007365745F54696D656F75740053797374656D2E54657874006F705F457175616C6974790053797374656D2E4E65742E53656375726974790049734E756C6C4F72456D70747900466C5F417574656E746963615F50726F7879000000010007470045005400000B5500540046002D0038000111680074007400700073003A002F002F0000053A003A00000950004F005300540000075000550054000000000091D3434B91A6944B9944F25BAF4B39B0000420010108032000010520010111112C07210E0E0E0E0E0E0E0E0208021229020202021D0E08081D0E0E0E0202020E1D05122D1231122D0212351239032000020320000E02060E03200008040001020E05000112550E042001010E042001080E040001010205000101115D052002011C180500010112410620011D0E1D030820021D0E1D0E11650420001269052002010E0E050002020E0E04000012710520011D050E042001010A042000122D072003011D050808042000127905000112710E07200201122D1271050001111D0E0307010208B77A5C561934E0890306120C0306124118000A01111D111D111D111D111D111D1121111D112510111D030000010A2004021C12451249114D0801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000701000000000401000000000000000000EB85E45D00000000020000001C0100003C2F00003C110000525344538863FD7F0DBEED47BBE2FD478149B89501000000433A5C55736572735C6469726365755C736F757263655C7265706F735C53514C434C525F5265717569736963616F5F5765625C53514C434C525F5265717569736963616F5F5765625C6F626A5C44656275675C53514C434C525F5265717569736963616F5F5765622E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000008030000000000000000000009A3000000020000000000000000000000000000000000000000000008C300000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000007C02000000000000000000007C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004DC010000010053007400720069006E006700460069006C00650049006E0066006F000000B801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000054001A00010049006E007400650072006E0061006C004E0061006D0065000000530051004C0043004C0052005F005200650071007500690073006900630061006F005F005700650062002E0064006C006C0000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000005C001A0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0043004C0052005F005200650071007500690073006900630061006F005F005700650062002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000AC3000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
    WITH PERMISSION_SET = EXTERNAL_ACCESS;


-- Adiciono o assembly como confiável
EXEC sys.sp_add_trusted_assembly @hash = @hash,
                                 @description = @clrName;


-- Desativo o modo TRUSTWORTHY do banco por motivos de segurança
ALTER DATABASE Base_Teste SET TRUSTWORTHY OFF
GO


-- Crio a Stored Procedure "stpWs_Requisicao"
CREATE PROCEDURE [dbo].[stpWs_Requisicao] @Ds_Url NVARCHAR (MAX), @Ds_Metodo NVARCHAR (MAX), @Ds_Parametros NVARCHAR (MAX), @Ds_Codificacao NVARCHAR (MAX), @Ds_Accept NVARCHAR (MAX), @Ds_ContentType NVARCHAR (MAX), @Fl_Autentica_Proxy BIT, @Ds_Headers NVARCHAR (MAX), @Qt_Segundos_Timeout INT, @Ds_Retorno_OUTPUT NVARCHAR (MAX) OUTPUT
AS EXTERNAL NAME [SQLCLR_Requisicao_Web].[StoredProcedures].[stpWs_Requisicao]

Código fuente C# de stpWs_Requisicao:

Si no desea crear el ensamblado que puse a disposición anteriormente y desea programar su SQLCLR usted mismo en Visual Studio y luego publicarlo en la base de datos, pondré a disposición el código fuente C# de este procedimiento almacenado.

using System;
using System.Data.SqlTypes;
using System.Net;
using System.IO;
using System.Text;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void stpWs_Requisicao(SqlString Ds_Url, SqlString Ds_Metodo, SqlString Ds_Parametros, SqlString Ds_Codificacao, SqlString Ds_Accept, SqlString Ds_ContentType, SqlBoolean Fl_Autentica_Proxy, SqlString Ds_Headers, SqlInt32 Qt_Segundos_Timeout, out SqlString Ds_Retorno_OUTPUT)
    {


        var parametros = (Ds_Parametros.IsNull) ? "" : Ds_Parametros.Value;
        var metodo = (Ds_Metodo.IsNull) ? "GET" : Ds_Metodo.Value;
        var url = Ds_Url.Value;
        var feedData = string.Empty;
        var encoding = (Ds_Codificacao.IsNull) ? "UTF-8" : Ds_Codificacao.Value;
        var accept = (Ds_Accept.IsNull) ? "" : Ds_Accept.Value;
        var contentType = (Ds_ContentType.IsNull) ? "" : Ds_ContentType.Value;
        var dsHeaders = (Ds_Headers.IsNull) ? "" : Ds_Headers.Value;
        var autenticaProxy = (!Fl_Autentica_Proxy.IsNull) && Fl_Autentica_Proxy.Value;
        var timeout = (Qt_Segundos_Timeout.IsNull) ? 30 : Qt_Segundos_Timeout.Value;


        if (string.IsNullOrEmpty(encoding))
            encoding = "UTF-8";


        try
        {

            var request = (HttpWebRequest)WebRequest.Create(url);
            request.Method = metodo;
            request.Timeout = timeout * 1000;


            if (url.IndexOf("https://") >= 0)
            {
                ServicePointManager.Expect100Continue = true;
                ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12 | SecurityProtocolType.Ssl3;
                ServicePointManager.ServerCertificateValidationCallback = delegate { return true; };
            }


            if (!string.IsNullOrEmpty(contentType))
            {
                request.ContentType = contentType;
            }


            if (!string.IsNullOrEmpty(accept))
            {
                request.Accept = accept;
            }


            if (!string.IsNullOrEmpty(dsHeaders))
            {

                var cabecalhos = dsHeaders.Split('|');
                var numeroCabecalhos = cabecalhos.Length;

                for (var i = 0; i < numeroCabecalhos; i++)
                {

                    var cabecalho = cabecalhos[i].Split(new string[] { "::" }, StringSplitOptions.None);
                    var descricao = cabecalho[0].Trim();
                    var valor = cabecalho[1].Trim();

                    request.Headers.Add(descricao, valor);
                }

            }


            if (autenticaProxy)
            {

                //if (!string.IsNullOrEmpty(Servidor.Ds_Proxy_Url))
                    //request.Proxy = new WebProxy(Servidor.Ds_Proxy_Url, Servidor.Ds_Proxy_Porta) { Credentials = new NetworkCredential(Servidor.Ds_Proxy_Usuario, Servidor.Ds_Proxy_Senha, Servidor.Ds_Proxy_Dominio) };

            }


            if ((metodo == "POST" || metodo == "PUT") && parametros.Length > 0)
            {
                var data = parametros;
                var dataStream = Encoding.UTF8.GetBytes(data);

                request.ContentLength = dataStream.Length;

                using (var newStream = request.GetRequestStream())
                {
                    newStream.Write(dataStream, 0, dataStream.Length);
                    newStream.Close();
                }
            }


            using (var response = (HttpWebResponse)request.GetResponse())
            {
                using (var stream = response.GetResponseStream())
                {
                    if (stream != null)
                    {
                        using (var streamReader = new StreamReader(stream, Encoding.GetEncoding(encoding)))
                        {
                            feedData = streamReader.ReadToEnd();
                        }
                    }
                }
            }


        }
        catch (Exception ex)
        {
            throw new ApplicationException(ex.Message);
        }

        Ds_Retorno_OUTPUT = feedData;

    }

};

Enviar mensajes a Whatsapp con SQLCLR

Haga clic aquí para ver el contenido
Ahora que he demostrado cómo conectarme a la API, haciendo posible enviar mensajes a Whatsapp con cualquier lenguaje de programación, demostraré cómo podemos hacerlo usando SQL Server. Para aquellos que ya siguen mi blog, saben que hay al menos dos formas sencillas de hacerlo: utilizando SQLCLR y OLE Automation.

Enviar mensajes privados a través de Whatsapp

La primera prueba a realizar es enviando mensajes privados a un contacto. Su uso es muy sencillo:

-- ALTERAR CONFORME NECESSIDADE
DECLARE
    @Destino VARCHAR(20) = 'telefone_destino', -- FORMATO: ddnumero Ex: 11988543306
    @Mensagem VARCHAR(MAX) = 'Olá! \nEsta é uma mensagem de teste.',
    @Chave VARCHAR(100) = 'Voz5dWkr5EEhBAbYuwJ2',
    @Usuario VARCHAR(100) = 'user',
    @Senha VARCHAR(100) = 'api'
    

-- PARÂMETROS FIXOS
DECLARE
    @Ds_Retorno_OUTPUT NVARCHAR(MAX),
    @Url VARCHAR(MAX) = 'http://api.meuaplicativo.vip:13005/services/message_send?line=5527981049976&destiny=' + @Destino + '&text=' + @Mensagem,
    @source VARBINARY(MAX) = CONVERT(VARBINARY(MAX), @Usuario + ':' + @Senha),
    @Paramatros VARCHAR(MAX) = 'App=NetiZap Consumers 1.0&AccessKey=' + @Chave,
    @Autorizacao VARCHAR(500),
    @Cabecalhos VARCHAR(MAX)
    

-- Gero a string base64 com o usuário e senha da API
SET @Autorizacao = 'Basic ' + CAST('' AS XML).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')
SET @Cabecalhos = 'Authorization:: ' + @Autorizacao


-- Faço a chamada à API
EXEC dbo.stpWs_Requisicao
    @Ds_Url = @Url, -- nvarchar(max)
    @Ds_Metodo = N'POST', -- nvarchar(max)
    @Ds_Parametros = @Paramatros, -- nvarchar(max)
    @Ds_Codificacao = N'utf-8', -- nvarchar(max)
    @Ds_Accept = N'', -- nvarchar(max)
    @Ds_ContentType = N'application/x-www-form-urlencoded', -- nvarchar(max)
    @Fl_Autentica_Proxy = 0, -- bit
    @Ds_Headers = @Cabecalhos, -- nvarchar(max)
    @Qt_Segundos_Timeout = 30, -- int
    @Ds_Retorno_OUTPUT = @Ds_Retorno_OUTPUT OUTPUT -- nvarchar(max)


-- Mostro o retorno da requisição na tela
SELECT @Ds_Retorno_OUTPUT

Resultado:

Cómo llega el mensaje al celular:

Cómo recuperar la lista de grupos disponibles

Para enumerar los grupos disponibles en su cuenta, puede utilizar el siguiente script. Para enviar un mensaje al grupo, simplemente use el mismo script de envío de mensajes privados y reemplace el número de teléfono del destinatario con la identificación del grupo que se devuelve a continuación.

El tratamiento JSON se realizó con la función OPENJSON, disponible en SQL Server 2016. Si su versión es anterior a esa, puede reemplazarla con un simple SELECT en la variable e identificar manualmente la identificación del grupo deseado. Si quieres saber más sobre el manejo de cadenas JSON, echa un vistazo a mi artículo. SQL Server 2016: uso de compatibilidad con JSON nativo (JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON, ISJSON, JSON_MODIFY).

-- ALTERAR CONFORME NECESSIDADE
DECLARE
    @Chave VARCHAR(100) = 'Voz5dWkr5EEhBAbYuwJ2',
    @Usuario VARCHAR(100) = 'user',
    @Senha VARCHAR(100) = 'api'
    

-- PARÂMETROS FIXOS
DECLARE
    @Ds_Retorno_OUTPUT NVARCHAR(MAX),
    @Url VARCHAR(MAX) = 'http://api.meuaplicativo.vip:13005/services/group_search?line=5527981049976&AccessKey=' + @Chave,
    @source VARBINARY(MAX) = CONVERT(VARBINARY(MAX), @Usuario + ':' + @Senha),
    @Autorizacao VARCHAR(500),
    @Cabecalhos VARCHAR(MAX)
    

-- Gero a string base64 com o usuário e senha da API
SET @Autorizacao = 'Basic ' + CAST('' AS XML).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')
SET @Cabecalhos = 'Authorization:: ' + @Autorizacao


-- Lista os grupos disponíveis para a minha conta
EXEC dbo.stpWs_Requisicao
    @Ds_Url = @Url, -- nvarchar(max)
    @Ds_Metodo = N'GET', -- nvarchar(max)
    @Ds_Parametros = NULL, -- nvarchar(max)
    @Ds_Codificacao = N'utf-8', -- nvarchar(max)
    @Ds_Accept = N'', -- nvarchar(max)
    @Ds_ContentType = N'application/x-www-form-urlencoded', -- nvarchar(max)
    @Fl_Autentica_Proxy = 0, -- bit
    @Ds_Headers = @Cabecalhos, -- nvarchar(max)
    @Qt_Segundos_Timeout = 30, -- int
    @Ds_Retorno_OUTPUT = @Ds_Retorno_OUTPUT OUTPUT -- nvarchar(max)


SELECT
    conteudo.id,
    conteudo.descricao
FROM 
    OPENJSON(@Ds_Retorno_OUTPUT, '$')
    WITH (
        [root] NVARCHAR(MAX) AS JSON
    ) AS elemento
    CROSS APPLY OPENJSON(elemento.[root]) WITH(
        id VARCHAR(200),
        descricao VARCHAR(500)
    ) AS conteudo

Resultado:

Cómo enviar mensajes a un grupo de Whatsapp

Ahora que he demostrado cómo enviar un mensaje privado y cómo enumerar grupos, ¡combinemos los dos y enviemos mensajes a un grupo!

-- ALTERAR CONFORME NECESSIDADE
DECLARE
    @Destino VARCHAR(50),
    @Mensagem VARCHAR(MAX) = 'Olá! \nEsta é uma mensagem de *teste* para o ~grupo~.',
    @Chave VARCHAR(100) = 'Voz5dWkr5EEhBAbYuwJ2',
    @Usuario VARCHAR(100) = 'user',
    @Senha VARCHAR(100) = 'api'
    

-- PARÂMETROS FIXOS
DECLARE
    @Ds_Retorno_OUTPUT NVARCHAR(MAX),
    @Url_Mensagem VARCHAR(MAX),
    @Url_Grupo VARCHAR(MAX) = 'http://api.meuaplicativo.vip:13005/services/group_search?line=5527981049976&AccessKey=' + @Chave,
    @source VARBINARY(MAX) = CONVERT(VARBINARY(MAX), @Usuario + ':' + @Senha),
    @Paramatros VARCHAR(MAX) = 'App=NetiZap Consumers 1.0&AccessKey=' + @Chave,
    @Autorizacao VARCHAR(500),
    @Cabecalhos VARCHAR(MAX)
    

-- Gero a string base64 com o usuário e senha da API
SET @Autorizacao = 'Basic ' + CAST('' AS XML).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')
SET @Cabecalhos = 'Authorization:: ' + @Autorizacao


-- Faço a chamada à API para recuperar a lista dos grupos
EXEC dbo.stpWs_Requisicao
    @Ds_Url = @Url_Grupo, -- nvarchar(max)
    @Ds_Metodo = N'GET', -- nvarchar(max)
    @Ds_Parametros = NULL, -- nvarchar(max)
    @Ds_Codificacao = N'utf-8', -- nvarchar(max)
    @Ds_Accept = N'', -- nvarchar(max)
    @Ds_ContentType = N'application/x-www-form-urlencoded', -- nvarchar(max)
    @Fl_Autentica_Proxy = 0, -- bit
    @Ds_Headers = @Cabecalhos, -- nvarchar(max)
    @Qt_Segundos_Timeout = 30, -- int
    @Ds_Retorno_OUTPUT = @Ds_Retorno_OUTPUT OUTPUT -- nvarchar(max)


-- Gravo a lista dos grupos numa tabela temporária
IF (OBJECT_ID('tempdb..#Grupos') IS NOT NULL) DROP TABLE #Grupos
SELECT
    conteudo.id,
    conteudo.descricao
INTO
    #Grupos
FROM 
    OPENJSON(@Ds_Retorno_OUTPUT, '$')
    WITH (
        [root] NVARCHAR(MAX) AS JSON
    ) AS elemento
    CROSS APPLY OPENJSON(elemento.[root]) WITH(
        id VARCHAR(200),
        descricao VARCHAR(500)
    ) AS conteudo

    
SET @Ds_Retorno_OUTPUT = NULL
SET @Destino = (SELECT TOP(1) id FROM #Grupos WHERE descricao = 'Teste API')
SET @Url_Mensagem = 'http://api.meuaplicativo.vip:13005/services/message_send?line=5527981049976&destiny=' + @Destino + '&text=' + @Mensagem


-- Faço a chamada à API para enviar a mensagem para o grupo
EXEC dbo.stpWs_Requisicao
    @Ds_Url = @Url_Mensagem, -- nvarchar(max)
    @Ds_Metodo = N'POST', -- nvarchar(max)
    @Ds_Parametros = @Paramatros, -- nvarchar(max)
    @Ds_Codificacao = N'utf-8', -- nvarchar(max)
    @Ds_Accept = N'', -- nvarchar(max)
    @Ds_ContentType = N'application/x-www-form-urlencoded', -- nvarchar(max)
    @Fl_Autentica_Proxy = 0, -- bit
    @Ds_Headers = @Cabecalhos, -- nvarchar(max)
    @Qt_Segundos_Timeout = 30, -- int
    @Ds_Retorno_OUTPUT = @Ds_Retorno_OUTPUT OUTPUT -- nvarchar(max)


-- Mostro o retorno da requisição na tela
SELECT @Ds_Retorno_OUTPUT

Resultado del grupo:

Cómo recuperar la lista de transmisiones disponibles para su cuenta

Casi idéntico al listado de grupos de cuentas, el siguiente script le permite enumerar las listas de transmisión disponibles para enviar a través de la API:

-- ALTERAR CONFORME NECESSIDADE
DECLARE
    @Chave VARCHAR(100) = 'Voz5dWkr5EEhBAbYuwJ2',
    @Usuario VARCHAR(100) = 'user',
    @Senha VARCHAR(100) = 'api'
    

-- PARÂMETROS FIXOS
DECLARE
    @Ds_Retorno_OUTPUT NVARCHAR(MAX),
    @Url VARCHAR(MAX) = 'http://api.meuaplicativo.vip:13005/services/broadcast_search?line=5527981049976&AccessKey=' + @Chave,
    @source VARBINARY(MAX) = CONVERT(VARBINARY(MAX), @Usuario + ':' + @Senha),
    @Autorizacao VARCHAR(500),
    @Cabecalhos VARCHAR(MAX)
    

-- Gero a string base64 com o usuário e senha da API
SET @Autorizacao = 'Basic ' + CAST('' AS XML).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')
SET @Cabecalhos = 'Authorization:: ' + @Autorizacao


-- Lista os grupos disponíveis para a minha conta
EXEC dbo.stpWs_Requisicao
    @Ds_Url = @Url, -- nvarchar(max)
    @Ds_Metodo = N'GET', -- nvarchar(max)
    @Ds_Parametros = NULL, -- nvarchar(max)
    @Ds_Codificacao = N'utf-8', -- nvarchar(max)
    @Ds_Accept = N'', -- nvarchar(max)
    @Ds_ContentType = N'application/x-www-form-urlencoded', -- nvarchar(max)
    @Fl_Autentica_Proxy = 0, -- bit
    @Ds_Headers = @Cabecalhos, -- nvarchar(max)
    @Qt_Segundos_Timeout = 30, -- int
    @Ds_Retorno_OUTPUT = @Ds_Retorno_OUTPUT OUTPUT -- nvarchar(max)


SELECT
    conteudo.id,
    conteudo.descricao
FROM 
    OPENJSON(@Ds_Retorno_OUTPUT, '$')
    WITH (
        [root] NVARCHAR(MAX) AS JSON
    ) AS elemento
    CROSS APPLY OPENJSON(elemento.[root]) WITH(
        id VARCHAR(200),
        descricao VARCHAR(500)
    ) AS conteudo

Resultado:

Entonces, ¿te gustó este consejo? Ahora puedes crear alertas, monitoreo, enviar archivos adjuntos, mensajes a grupos, listas de difusión e incluso bots usando esta API y totalmente integrada con tu base de datos.
¡Un abrazo grande y hasta la próxima!