sp_OAGetProperty returning NULL with OUT variable declared as MAX

Question

I have the following problem:

Within a function I'm using an OLE object to make HTTP requests. The function is declared like this:

function [dbo].[FN_GetRequestHTTP](@url varchar(2048), @reponseType varchar(10) = 'text')
returns @responseTable table
(
    StatusCode nvarchar(32),
    StatusText nvarchar(32),
    ResponseText nvarchar(max),
    SpErrorMessage varchar(max)
) as
begin
    declare @responseText nvarchar(4000);
    declare @ret int;
    declare @status nvarchar(32);
    declare @statusText nvarchar(32);
    declare @spErrorMessages nvarchar(4000);
    declare @token int;

    -- Open the connection
    exec @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token out;

    if @ret <> 0
    begin
        insert into @responseTable (spErrorMessage)
        values ('Unable to open HTTP connection');

        return;
    end

    -- Send the request
    exec @ret = sp_OAMethod @token, 'open', null, 'GET', @url, 'false';
    exec @ret = sp_OAMethod @token, 'send', null, null;

    -- Handle the response
    exec @ret = sp_OAGetProperty @token, 'status', @status out;
    exec @ret = sp_OAGetProperty @token, 'statusText', @statusText out;

    if @reponseType = 'binary'
    begin
        declare @binaryResult varbinary(8000);
        exec sp_OAGetProperty @token, 'responseBody', @binaryResult out;

        set @responseText = upper(sys.fn_varbintohexstr(@binaryResult));
    end

    else
        exec sp_OAGetProperty @token, 'responseText', @responseText out

    -- Close the connection
    exec @ret = sp_OADestroy @token;

    if @ret <> 0
    begin
        insert into @responseTable (spErrorMessage)
        values ('Unable to close HTTP connection')

        return;
    end

    insert into @responseTable
    values (@status, @statusText, @responseText, null);

    return;
end

This way the function works as intended: it can make HTTP requests and return the specified info in the format I need it.

The problem comes when I declare the variable declare @responseText nvarchar(4000); without a fixed length. I need it to be declare @responseText nvarchar(max); and the variable declare @binaryResult varbinary(8000); as declare @binaryResult varbinary(max);, because I can receive longer responses.

When declared as max, the value returned with exec sp_OAGetProperty @token, 'responseText', @responseText out is NULL, even though I get the correct result when using the fixed value (4000).

Which workarounds can I follow to accomplish what I seek and why does that happen with the procedure sp_OAGetProperty? because I created a dummy procedure as the following:

create procedure SP_ToDeleteAfterTest (@temp varchar(50) out)
as
begin
    set @temp = 'Testing';

    print 'Done'
end

And executed it like this:

declare @dunno varchar(max)
exec dbo.SP_ToDeleteAfterTest @dunno out

select @dunno

And it did work properly.

Is it because there are constraints with the procedure sp_OAGetProperty?

Edit

Following the recommendation given in the comments' section, here is the implementation of the desired behavior using a stored procedure:

create procedure [dbo].[SP_GetRequestHTTP](@url varchar(2048), @reponseType varchar(10) = 'text')
as
begin
    declare @responseTable table
    (
        StatusCode nvarchar(32),
        StatusText nvarchar(32),
        ResponseText nvarchar(max),
        SpErrorMessage varchar(max)
    );

    declare @responseText table(content nvarchar(max));
    declare @ret int;
    declare @status nvarchar(32);
    declare @statusText nvarchar(32);
    declare @spErrorMessages nvarchar(4000);
    declare @token int;

    -- Open the connection
    exec @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token out;

    if @ret <> 0
    begin
        insert into @responseTable (spErrorMessage)
        values ('Unable to open HTTP connection');
        
        select * from @responseTable;

        return;
    end

    -- Send the request
    exec @ret = sp_OAMethod @token, 'open', null, 'GET', @url, 'false';
    exec @ret = sp_OAMethod @token, 'send', null, null;

    -- Handle the response
    exec @ret = sp_OAGetProperty @token, 'status', @status out;
    exec @ret = sp_OAGetProperty @token, 'statusText', @statusText out;

    if @reponseType = 'binary'
    begin
        declare @binaryResult table(content varbinary(max));
        insert into @binaryResult exec sp_OAGetProperty @token, 'responseBody';
        
        insert into @responseText select top 1 sys.fn_varbintohexstr(content) from @binaryResult;
    end

    else
        insert into @responseText exec sp_OAGetProperty @token, 'responseText'

    -- Close the connection
    exec @ret = sp_OADestroy @token;

    if @ret <> 0
    begin
        insert into @responseTable (spErrorMessage)
        values ('Unable to close HTTP connection')
        
        select * from @responseTable;

        return;
    end

    insert into @responseTable
    values (@status, @statusText, (select top 1 * from @responseText), null);
    
    select * from @responseTable;

    return;
end

Answer 1

If you need to get a response in XML format, you should do like this:

declare @Result int
declare @xml table(Content xml)

INSERT INTO @xml(Content)
EXEC @Result = sp_OAGetProperty @token, 'responseXML.xml'--, @responseText OUT

select * from @xml

Instead of

exec sp_OAGetProperty @token, 'responseText', @responseText out

Follow this link: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ac224833-1ffa-4008-880c-ce8f4d442110/trying-to-obtain-xml-data-from-url-via-sql?forum=sqlxml