存储过程封装web service

sp_configure ‘show advanced options‘, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ole Automation Procedures‘, 1;
GO
RECONFIGURE;
GO

CREATE   PROC fn_et_geocode(@log VARCHAR(20),@lat VARCHAR(20),@result VARCHAR(8000) OUTPUT)
as 

DECLARE @ServiceUrl AS VARCHAR(1000); 
DECLARE @UrlAddress VARCHAR(500);

--WebService地址：以http开头，结尾带斜杠，例如‘http://webservice.webxml.com.cn/WebServices/MobileCodeWS.asmx/‘ 
SET @UrlAddress = ‘http://restapi.amap.com/v3/geocode/‘;

DECLARE @FunName VARCHAR(50);
--WebService中调用的方法名：例如‘getMobileCodeInfo‘
SET @FunName = ‘regeo‘;   

--以下参数对应WebService中参数的[参数名]
DECLARE @output VARCHAR(20)
  , @location VARCHAR(200)
  , @key VARCHAR(200)
  , @radius VARCHAR(10)
  , @extensions VARCHAR(10);
SET @output = ‘json‘;
SET @location = @log+‘,‘+@lat;
SET @key = ‘xxxxxxxxxxxxxxxx‘;
SET @radius = ‘1000‘;
SET @extensions = ‘base‘;

SET @ServiceUrl = @UrlAddress + @FunName + ‘?output=‘ + @output + ‘&location=‘
    + @location + ‘&key=‘ + @key + ‘&radius=‘ + @radius + ‘&extensions=‘
    + @extensions;                     

--- 真正调用的是下面的这些语句,上面的都是拼接API地址.        
DECLARE @Object AS INT;
DECLARE @ResponseText AS VARCHAR(8000);
                  
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;
     
SET @result =  @ResponseText;     
EXEC sp_OADestroy @Object;
GO

DECLARE @geojson VARCHAR(8000)
EXEC fn_et_geocode ‘113.14052842882‘,‘27.81207139757‘,@geojson OUTPUT
SELECT @geojson