Realizando requisições POST e GET utilizando CLR (C#) no SQL Server

Post Views 4,976 views

Neste artigo

Reading time 4 minutes

Olá pessoal,
Boa tarde!

Neste post estarei demonstrando pra vocês como realizar requisições POST e GET (na verdade, pode ser qualquer tipo) em um banco de dados SQL Server utilizando o SQL CLR.

Se você não sabe o que é CLR ou tem dúvidas sobre como utilizar, como publicar, etc, veja este post aqui.

Eu já havia feito alguns posts aqui no blog demonstrando como fazer isso utilizando OLE Automation, mas como o CLR é uma alternativa muito mais segura e flexível, resolvi demonstrar como fazer isso utilizando o CLR (C#).

Código-fonte da procedure stpWs_Requisicao:

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
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, out SqlString Ds_Retorno_OUTPUT)
    {

        var parametros = (Ds_Parametros.IsNull) ? "" : Ds_Parametros.Value;
        var metodo = (Ds_Metodo.IsNull) ? "GET" : Ds_Metodo.Value.ToUpper();
        var url = Ds_Url.Value;
        var feedData = string.Empty;
        var encoding = (Ds_Codificacao.IsNull) ? "UTF-8" : Ds_Codificacao.Value;

        try
        {
            var request = (HttpWebRequest) WebRequest.Create(url);
            request.Method = metodo;
            request.ContentType = "application/x-www-form-urlencoded";
            
            if (metodo == "POST" && parametros.Length > 0)
            {
                var data = parametros;
                var dataStream = Encoding.UTF8.GetBytes(data);

                request.ContentLength = dataStream.Length;

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

            var response = (HttpWebResponse) request.GetResponse();
            var stream = response.GetResponseStream();
            var streamReader = new StreamReader(stream, Encoding.GetEncoding(encoding));
            feedData = streamReader.ReadToEnd();

            response.Close();
            stream?.Dispose();
            streamReader.Dispose();
        }
        catch (Exception ex)
        {
            if (SqlContext.Pipe != null) SqlContext.Pipe.Send(ex.Message);
        }

        Ds_Retorno_OUTPUT = feedData;

    }

}

using System;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

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, out SqlString Ds_Retorno_OUTPUT)

{

var parametros = (Ds_Parametros.IsNull) ? "" : Ds_Parametros.Value;

var metodo = (Ds_Metodo.IsNull) ? "GET" : Ds_Metodo.Value.ToUpper();

var url = Ds_Url.Value;

var feedData = string.Empty;

var encoding = (Ds_Codificacao.IsNull) ? "UTF-8" : Ds_Codificacao.Value;

try

{

var request = (HttpWebRequest) WebRequest.Create(url);

request.Method = metodo;

request.ContentType = "application/x-www-form-urlencoded";

if (metodo == "POST" && parametros.Length > 0)

{

var data = parametros;

var dataStream = Encoding.UTF8.GetBytes(data);

request.ContentLength = dataStream.Length;

var newStream = request.GetRequestStream();

newStream.Write(dataStream, 0, dataStream.Length);

newStream.Close();

}

var response = (HttpWebResponse) request.GetResponse();

var stream = response.GetResponseStream();

var streamReader = new StreamReader(stream, Encoding.GetEncoding(encoding));

feedData = streamReader.ReadToEnd();

response.Close();

stream?.Dispose();

streamReader.Dispose();

}

catch (Exception ex)

{

if (SqlContext.Pipe != null) SqlContext.Pipe.Send(ex.Message);

}

Ds_Retorno_OUTPUT = feedData;

}

Exemplo de utilização

Uma vez que a procedure já tenha sido criada e o assembly tenha sido publicado no seu servidor, basta utilizar a SP para realizar suas requisições normalmente, como os exemplos abaixo:

DECLARE
    @Url VARCHAR(MAX) = 'http://viacep.com.br/ws/29090090/xml',
    @Retorno VARCHAR(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'ISO-8859-1', -- nvarchar(max)
    @Ds_Retorno_OUTPUT = @Retorno OUT -- nvarchar(max)


SELECT CAST(@Retorno AS xml)

DECLARE

@Url VARCHAR(MAX) = 'http://viacep.com.br/ws/29090090/xml',

@Retorno VARCHAR(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'ISO-8859-1', -- nvarchar(max)

@Ds_Retorno_OUTPUT = @Retorno OUT -- nvarchar(max)

SELECT CAST(@Retorno AS xml)

Como criar as bibliotecas do SQLCLR apenas com T-SQL

Clique aqui para visualizar o conteúdo

Caso você queira utilizar o SQLCLR, esse tópico serve para instalar os objetos necessários para conseguir seguir os exemplos desse artigo, sem precisar criar um novo projeto do Visual Studio e fazer o deploy manualmente do projeto.

A forma mais simples e robusta de se realizar requisições HTTP pelo SQL Server é, sem dúvidas, utilizando o SQLCLR (clique aqui se você não sabe nem o que é o SQLCLR).

Como criar a stpWs_Requisicao no seu ambiente (SQL Server 2012 ao 2016):

Caso você esteja utilizando as versões 2012, 2014 ou 2016 do SQL Server, pode utilizar o script abaixo para criar o assembly (DLL) no banco de dados desejado e a stored procedure “stpWs_Requisicao”, que é utilizada para realizar a requisição HTTP da 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]

USE [Base_Teste]

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

Como criar a stpWs_Requisicao no seu ambiente (SQL Server 2017+):

Caso você esteja utilizando uma versão superior ou igual a 2017 do SQL Server, pode utilizar o script abaixo para criar o assembly (DLL) no banco de dados desejado e a stored procedure “stpWs_Requisicao”, que é utilizada para realizar a requisição HTTP da API. Esse script é diferente do anterior, pois a partir da versão 2017, o SQL Server agora exige a assinatura de assemblies até mesmo no modo Safe, devido à mudanças na segurança do .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]

USE [Base_Teste]

-- Habilito o modo TRUSTWORTHY para conseguir criar o assembly no modo EXTERNAL_ACCESS

ALTER DATABASE Base_Teste SET TRUSTWORTHY ON

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

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

-- Crio a Stored Procedure "stpWs_Requisicao"

AS EXTERNAL NAME [SQLCLR_Requisicao_Web].[StoredProcedures].[stpWs_Requisicao]

Próximos passos

Agora que vocês já aprenderam a realizar as requisições a partir do próprio banco de dados, vocês podem implementar as SP’s abaixo no seu banco de dados, desta vez, utilizando o poder do CLR:

A alteração é bem simples, basta trocar estas instruções:

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

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

por essa:

EXEC CLR.dbo.stpWs_Requisicao
    @Ds_Url = @Url, -- nvarchar(max)
    @Ds_Metodo = N'GET', -- nvarchar(max)
    @Ds_Parametros = N'', -- nvarchar(max)
    @Ds_Codificacao = N'ISO-8859-1', -- nvarchar(max)
    @Ds_Retorno_OUTPUT = @Retorno OUT -- nvarchar(max)


SET @XML = CAST(@Retorno AS XML)

EXEC CLR.dbo.stpWs_Requisicao

@Ds_Url = @Url, -- nvarchar(max)

@Ds_Metodo = N'GET', -- nvarchar(max)

@Ds_Parametros = N'', -- nvarchar(max)

@Ds_Codificacao = N'ISO-8859-1', -- nvarchar(max)

@Ds_Retorno_OUTPUT = @Retorno OUT -- nvarchar(max)

SET @XML = CAST(@Retorno AS XML)

Bem mais simples, não ?
Um abraço e até o próximo post!

sql server clr integração integration post get requisições requests database banco de dados c# csharp sqlclr

Leandro Assis disse:

August 18, 2021 às 10:45

Muito bom, Dirceu! ??

Responder
Leandro disse:

October 30, 2019 às 11:52

Bom dia.
Estou testando o código, porém meu @Ds_Retorno_OUTPUT=null e meu @return_value =0.
Sabe o que pode estar dando errado?. Estou precisando muito desse codigo para usar o banco como gatilho para fazer requisiçoes em um webservice, visto ser o unico meio que encontrei para integrar um sistema de automação com uma api web.

Responder

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

Realizando requisições POST e GET utilizando CLR (C#) no SQL Server

Código-fonte da procedure stpWs_Requisicao:

Exemplo de utilização

Como criar as bibliotecas do SQLCLR apenas com T-SQL

Como criar a stpWs_Requisicao no seu ambiente (SQL Server 2012 ao 2016):

Como criar a stpWs_Requisicao no seu ambiente (SQL Server 2017+):

Próximos passos

sql server clr integração integration post get requisições requests database banco de dados c# csharp sqlclr

You may also like...

2 Responses

Leave a Reply Cancel reply

Código-fonte da procedure stpWs_Requisicao:

Exemplo de utilização

Como criar as bibliotecas do SQLCLR apenas com T-SQL

Como criar a stpWs_Requisicao no seu ambiente (SQL Server 2012 ao 2016):

Como criar a stpWs_Requisicao no seu ambiente (SQL Server 2017+):

Próximos passos

sql server clr integração integration post get requisições requests database banco de dados c# csharp sqlclr

You may also like...

Como utilizar a API do Pushbullet para enviar torpedos SMS no C#, PHP, Java ou pelo SQL Server (com CLR)

SQL Server - How to avoid brute force attacks on your database

SQL Server – Como utilizar o comando MERGE para inserir, atualizar e apagar dados com apenas 1 comando

2 Responses

Leave a Reply Cancel reply