sql调用web服务

基本存储过程:
/****** Object: StoredProcedure [dbo].[spCallBoService] Script Date: 11/19/2015 16:45:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spCallBoService]
(
@Url NVARCHAR(MAX) = 'http://192.168.0.151:8085/BillService.asmx/' ,
@MethodName VARCHAR(50) = 'HelloWorld' ,
@Parameters NVARCHAR(MAX) = '' ,
@result VARCHAR(MAX) OUT
)
AS
DECLARE @obj INT
DECLARE @ValorDeRegreso INT
DECLARE @response VARCHAR(8000)
SET @Url = @Url + @MethodName
IF LEN(@Parameters) > 0
BEGIN
SET @Url += '?' + @Parameters
END
PRINT @Url
EXEC sp_OACreate 'MSXML2.XMLHttp', @obj OUT
EXEC sys.sp_OAMethod @obj, 'Open', NULL, 'Get', @Url, false
EXEC sp_OAMethod @obj, 'send'
EXEC sys.sp_OAGetProperty @obj, 'responseText', @response OUT
SET @result = @response
EXEC sys.sp_OADestroy @obj

 

使用方法:
/****** Object: StoredProcedure [dbo].[sp_CallSynFailureSendMCodeToMticket] Script Date: 11/19/2015 16:48:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Author: 曾添利
-- Create date: 2015-11-18
-- Description: 定时同步大转盘m券推送券系统失败的m券
-- =============================================
CREATE PROCEDURE [dbo].[sp_CallSynFailureSendMCodeToMticket]
AS
BEGIN
DECLARE @result NVARCHAR(1000)
EXEC dbo.spCallBoService @Url = N'http://192.168.0.37:8080/WebService.asmx/', -- nvarchar(max)
@MethodName = 'ExecuteControlFunction', -- varchar(50)
@Parameters = N'paramJson=["MyjWeiXin.Controllers.Activity.Roulette.RoulettePage","SynFailureSendMCodeToMticket"]', -- nvarchar(max)
@result = @result OUTPUT -- varchar(max)
PRINT @result

END