sql 调用 webservice

　　前面一篇是调用webapi,把调用webservice的也贴出来看看

USE [CarRental]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[IO_GPSServiceChangeVehicleShiKongState]
as
begin
begin transaction 
    begin
        declare @LicNo varchar(100)--车牌号
        declare @OrganizeCode varchar(100)
        declare @ZhanCheState int=1 --占车状态
        declare cus_zhanche cursor for(
            select distinct LicNo ,THEIRSHOP from Car_CarInfo
            where ServiceStatus3=70
        )
        open cus_zhanche
        fetch next from cus_zhanche into @LicNo,@OrganizeCode
        while @@fetch_status=0
        begin
            declare @FunName varchar(50) --函数名称
            declare @ServiceUrl varchar(1000) --服务请求
            declare @UrlAddress varchar(500)--服务地址
            declare @SelectFlag varchar(100)
        --WebService地址
        set @UrlAddress = 'http://123.126.34.25:2488/GpsService.asmx/'    --测试地址
        --参数进行编码
        select @LicNo=dbo.UrlEncode(@LicNo);
        select @ZhanCheState=dbo.UrlEncode(@ZhanCheState);
        set @FunName='ChangeVehicleShiKongState'
        set @ServiceUrl=@UrlAddress+@FunName+'?UserName=webdemo&Password=gps123456&TenantCode=2009092500005&'
        +'&licenceNumber='+@LicNo+'&organizeCode='+@OrganizeCode+'&IsShiKong='+ cast(@ZhanCheState as varchar(8));
        --执行请求
        --print (@ServiceUrl)
        --测试代码
        Declare @Object0 as Int
        Declare @ResponseText0 as Varchar(8000)
        DECLARE @ItemMessage0 XML                
        Exec sp_OACreate 'MSXML2.XMLHTTP', @Object0 OUT;
        Exec sp_OAMethod @Object0, 'open', NULL, 'get',@ServiceUrl,'false'
        Exec sp_OAMethod @Object0, 'send'
        Exec sp_OAMethod @Object0, 'responseText', @ResponseText0 OUTPUT
        set @ItemMessage0=convert(xml,Replace(@ResponseText0,'<?xml version="1.0" encoding="utf-8"?>',''));        
        Select @SelectFlag=T.c.value('(Success/text())[1]','NVARCHAR(10)') 
        from @ItemMessage0.nodes('/root') AS T(c);
        --if(@SelectFlag='1')
        --begin
        --测试代码
        --print(@SelectFlag)
        --测试代码
            insert into IO_GpsService_RequsetLog(GpsServiceFunName,GpsServiceParam,GpsServiceResult,GpsRequest,GpsServicXML,RequestTime,RequsetSource)
            Select @FunName,'占车信息推送',@SelectFlag,@ServiceUrl,@ResponseText0,getdate(),'System.ChangeVehicleShiKongState'
            from @ItemMessage0.nodes('/root') AS T(c)
        --end
        Exec sp_OADestroy @Object0
        fetch next from cus_zhanche into @LicNo,@OrganizeCode
    end
        close cus_zhanche
        deallocate cus_zhanche
end
commit transaction
return 1
if @@error<>0
rollback transaction
end