Stored Procedures Encapsulate Web service

Shang, reprint please keep http://www.cnblogs.com/BinBinGo/p/6959569.html.

SQL can also call the Web service

The steps are as follows:

1 Modifying the configuration

' Show advanced Options ' 1 ; GO RECONFIGURE ; GO  'Ole Automation procedures'1; GO RECONFIGURE ; GO

2 encapsulating the calling procedure in a stored procedure, this is the case of my reference to the German map.

CREATE   PROCFn_et_geocode (@log VARCHAR( -),@lat VARCHAR( -),@result VARCHAR(8000) OUTPUT) as DECLARE @ServiceUrl  as VARCHAR( +); DECLARE @UrlAddress VARCHAR( -);--WebService Address: Start with HTTP, end with Slash, for example ' http://webservice.webxml.com.cn/WebServices/MobileCodeWS.asmx/'SET @UrlAddress = 'http://restapi.amap.com/v3/geocode/';DECLARE @FunName VARCHAR( -);--method name called in WebService: for example ' Getmobilecodeinfo 'SET @FunName = 'Regeo'; --The following parameter corresponds to the [parameter name] of the parameter in WebService.DECLARE @output VARCHAR( -)  , @location VARCHAR( $)  , @key VARCHAR( $)  , @radius VARCHAR(Ten)  , @extensions VARCHAR(Ten);SET @output = 'JSON';SET @location = @log+','+@lat;SET @key = 'xxxxxxxxxxxxxxxx';SET @radius = ' +';SET @extensions = 'Base';SET @ServiceUrl = @UrlAddress + @FunName + '? output=' + @output + '&location='    + @location + '&key=' + @key + '&radius=' + @radius + '&extensions='    + @extensions;
---actually call the following statements, all of which are splicing API addresses. DECLARE @Object  as INT;DECLARE @ResponseText  as VARCHAR(8000); EXECsp_OACreate'msxml2.xmlhttp',@ObjectOut ;EXECsp_OAMethod@Object,'Open',NULL,'Get',@ServiceUrl,'false';EXECsp_OAMethod@Object,'Send';EXECsp_OAMethod@Object,'ResponseText',@ResponseTextOUTPUT; SET @result =  @ResponseText; EXECsp_OADestroy@Object;GO

3 Executing stored procedures

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

Stored Procedures Encapsulate Web service