SQL Server 2016 stored procedure to function

Question

I've created a stored procedure that uses a temp table to take the massive results of a Bing maps XML and convert it to varchar so that I can use it to retrieve several fields. The reason for converting it to varchar was because the XML was too large for the sp_OAMethod and my variable was always blank.

set @serviceUrl = 'https://dev.virtualearth.net/REST/v1/Routes/Truck?wp.0=' + @ToAddress  + '&wp.1=' + @FromAddress + '&vehicleHazardousMaterials=Flammable&output=xml&key=XXX-000-XXX'

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',@serviceUrl, 'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

exec @returnCode = sp_oamethod @Object, 'read', @ResponseText out, -1

Create table #tmp(dt xml)

insert into #tmp
    exec @hr = sp_OAGetProperty @Object, 'responseXML.XML'

Set @ResponseText = Convert(varchar(max), (SELECT dt from #tmp))
Drop Table #tmp 

select REPLACE(SUBSTRING(@ResponseText,PATINDEX('%<TravelDistance>%',@ResponseText),21),'<TravelDistance>','') Miles

  ,REPLACE(REPLACE(SUBSTRING(@ResponseText,PATINDEX('%<TravelDuration>%',@ResponseText),21),'<TravelDuration>',''),'<','')/60 TravelMinutes

Above is not the complete code; I've edited it severely to make it easier to read.

I'm sure there is a better way to do it but this works and it's very exciting.

However, the plan was to create a function that could be used along with other ad-hoc queries to retrieve the same data. Not possible as a function because of the temp table or populating a table from within the function.

So does anyone have a way to make this possible?

Answer 1

You really should use CLR for this. You're a long way from a correct stored procedure here, and the sp_OAxxx procedures are best avoided. You're not handling the return code correctly, you're using the wrong HTTP component, and not parsing the results using the SQL Server's XML parser. It may seem initially like a CLR implementation is more work, but maintaining code that uses sp_OAxxx stored procedures and COM interop will always be painful.

There are very few people who understand how this code works, how to change it, and whether it's safe and reliable. The next person to own your codebase is probably not one of those people. It's not good to have code like that in your project.

If you do use CLR, you still shouldn't use a function (although you technically can). You shouldn't perform any external access in the middle of a query.

Anyway here's some fixes to your existing approach:

declare @serviceURL varchar(max)
declare @returnCode int
declare @errorMessage nvarchar(max)
declare @Object int
declare @hr int
declare @key varchar(2000) = 'Aoj...nFS'
declare @FromAddress nvarchar(max) = '7000 N SH 161, Irving TX 75039'

declare @ToAddress nvarchar(max) = '8617 Garland Rd, Dallas, TX 75218'

set @serviceUrl = 'https://dev.virtualearth.net/REST/v1/Routes/Truck?wp.0=' + @ToAddress  + '&wp.1=' + @FromAddress + '&vehicleHazardousMaterials=Flammable&output=xml&key=' + @key


exec @hr = sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;
IF @hr <> 0
begin
    set @errorMessage = concat('sp_OACreate failed ', convert(varchar(20),cast(@hr as varbinary(4)),1));
    throw 60000, @errorMessage, 1;
end;
begin try
    Exec @hr = sp_OAMethod @Object, 'open', NULL, 'get',@serviceUrl, 'false'
    IF @hr <> 0
    begin
        set @errorMessage = concat('open failed ', convert(varchar(20),cast(@hr as varbinary(4)),1));
        throw 60000, @errorMessage, 1;
    end;

    Exec @hr = sp_OAMethod @Object, 'send'
    IF @hr <> 0
    begin
        set @errorMessage = concat('send failed ', convert(varchar(20),cast(@hr as varbinary(4)),1));
        throw 60000, @errorMessage, 1;
    end;

    declare @responseCode int;
    Exec @hr = sp_OAGetProperty @Object, 'status', @responseCode out
    IF @hr <> 0 or @responseCode <> 200
    begin
        set @errorMessage = concat('send failed hr:', convert(varchar(20),cast(@hr as varbinary(4)),1),' http response code: ', @responseCode);
        throw 60000, @errorMessage, 1;
    end;

    declare @tmp table(doc xml)
    insert into @tmp
    exec @hr = sp_OAGetProperty @Object, 'ResponseXML.XML'
    IF @hr <> 0
    begin
        set @errorMessage = concat('ResponseXML.XML failed ', convert(varchar(20),cast(@hr as varbinary(4)),1));
        throw 60000, @errorMessage, 1;
    end;



    WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/search/local/ws/rest/v1')
    select doc.value('(//TravelDistance)[1]', 'float') TravelDistance,
           doc.value('(//DistanceUnit)[1]', 'nvarchar(20)') DistanceUnit,
           doc.value('(//TravelDuration)[1]', 'float') TravelDuration,
           doc.value('(//DurationUnit)[1]', 'nvarchar(20)') DurationUnit
    from @tmp;
end try
begin catch
    exec @hr = sp_OADestroy  @Object ;
    throw;    
end catch