SQLSEVER 存储过程代码(定时任务调用WEBAPI存储过程)
/* 参数说明? @URL=http请求地址 @status=状态代? @returnText=返回? @object=对象令牌 @errSrc=错误源编? */ CREATE PROCEDURE [dbo].[P_GET_HttpRequestData]( @URL VARCHAR(500), @status INT=0 OUT, @returnText VARCHAR(2000)='' OUT ) AS BEGIN DECLARE @object INT, @errSrc INT /*初始化对*/ EXEC @status = SP_OACreate 'Msxml2.ServerXMLHTTP.3.0', @object OUT IF @status <> 0 BEGIN EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText OUT RETURN END /*创建链接*/ EXEC @status= SP_OAMethod @object,'open',NULL,'GET',@URL IF @status <> 0 BEGIN EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText OUT RETURN END EXEC @status=SP_OAMethod @object,'setRequestHeader','Content-Type','application/x-www-form-urlencoded' /*发起请求*/ EXEC @status= SP_OAMethod @object,'send',NULL IF @status <> 0 BEGIN EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText OUT RETURN END /*获取返回*/ EXEC @status= SP_OAGetProperty @object,'responseText',@returnText OUT IF @status <> 0 BEGIN EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText OUT RETURN END END; GO
SQLSEVER 定时任务 T-SQL
use 数据库服务器名 go DECLARE @returnText VARCHAR(2000) DECLARE @status int DECLARE @urlStr VARCHAR(255) SET @urlStr = '调用的WEBAPI'; EXEC P_GET_HttpRequestData @urlStr, @status OUTPUT, @returnText
编写业务逻辑的API,执行调用过程,实现定时任务