預存程序封裝web service

標籤：url   roc   obj   varchar   char   tom   name   text   json   

卞功鑫  ，轉載請保留http://www.cnblogs.com/BinBinGo/p/6959569.html。

sql 也可以調用web service

步驟如下:

 

1 修改配置

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

 

 

2 把調用過程封裝在預存程序中,這是我引用高德地圖的案例 

 

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

 

 

 

3  執行預存程序

 

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

 

預存程序封裝web service