前面一篇是调用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