首页 > 代码库 > 存储过程封装web service

存储过程封装web service

2024-10-02 15:59:39   213人阅读

作者：卞功鑫  ，转载请保留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


联系
我们