SQLSERVER调用WebAPI和WebService

S3_Leo

1. ----MSSQL调用API接口函数
   
2. CREATE/ALTER PROCEDURE [dbo].[ProS3_CallWebApi]
   
3.     @ApiUrl VARCHAR(200), --api请求地址
   
4.     @BodyJsom varchar(max),
   
5.     @ResponseText NVARCHAR(4000) OUTPUT
   
6. AS
   
7. BEGIN
   
8. 
   
9.        SET NOCOUNT ON;           
   
10.        Declare @Object as Int
    
11.   Exec sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @Object OUT;
    
12.   Exec sp_OAMethod @Object, 'open', NULL, 'POST',@ApiUrl,'false'
    
13.   Exec sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type','application/json'
    
14.   Exec sp_OAMethod @Object, 'setRequestHeader', NULL, 'Accept','S3YiFeiAPI'
    
15.   Exec sp_OAMethod @Object, 'send', NULL, @BodyJsom   --发送数据
    
16.   Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
    
17.   --EXEC sp_OAGetErrorInfo @Object --异常输出
    
18.   Exec sp_OADestroy @Object  -- 释放对象
    
19.   
    
20. END
    
21. 
    
22. ---------MSSQL调用函数
    
23. 
    
24. 
    
25. declare @ApiUrl as varchar(200)
    
26. set @ApiUrl = 'http://192.168.56.122:9023/YiFeiAPI?ApiName=GetSQL&ApiType=GET'
    
27. DECLARE @BodyJsom varchar(max)
    
28. set @BodyJsom ='{ "xFields": "MB001,MB002,MB064", "xDBTabel": "DEMO..INVMB", "xWhere": "MB001 IN (''21001'',''21002'')", "xGroup": "MB001,MB002,MB064", "xOrder": "MB001 DESC"}'   
    
29. DECLARE  @ResponseText NVARCHAR(4000)
    
30. Exec S3DB..[ProS3_CallWebApi] @ApiUrl,@BodyJsom,@ResponseText OUTPUT
    
31. select @ResponseText

复制代码

1. 开启组件
   
2. sp_configure 'show advanced options', 1;
   
3. GO
   
4. RECONFIGURE;
   
5. GO
   
6. sp_configure 'Ole Automation Procedures', 1;
   
7. GO
   
8. RECONFIGURE;
   
9. GO
   
10. EXEC sp_configure 'Ole Automation Procedures';
    
11. GO

复制代码

1. declare @ServiceUrl as varchar(1000)
   
2. set @ServiceUrl = 'http://172.16.170.192:803/api/wardRound/GetInfo'
   
3. DECLARE @data varchar(max);
   
4. --发送数据
   
5. set @data='{"ID": 67,"UserID": 10}'                  
   
6. 
   
7. Declare @Object as Int
   
8. Declare @ResponseText AS  varchar(8000)   ;      
   
9. Exec sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @Object OUT;
   
10. Exec sp_OAMethod @Object, 'open', NULL, 'POST',@ServiceUrl,'false'
    
11. Exec sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type','application/json;charset=UTF-8'
    
12. Exec sp_OAMethod @Object, 'send', NULL, @data --发送数据
    
13. Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
    
14. --EXEC sp_OAGetErrorInfo @Object --异常输出
    
15. Select  @ResponseText
    
16. Exec sp_OADestroy @Object
    
17. GO

复制代码

1. DECLARE @sUrl NVARCHAR(100)=N'http://127.0.0.1/WebService/Dye.asmx?op=ColorSystem'
   
2. 
   
3. DECLARE @sSoapContent NVARCHAR(2000)=N''
   
4. 
   
5. SELECT @sSoapContent=N'<?xml version="1.0" encoding="utf-8"?>
   
6. <soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
   
7.   <soap12:Body>
   
8.     <ColorSystem xmlns="http://tempuri.org/">
   
9.       <iCompanyId>1</iCompanyId>
   
10.       <sColorNo>2</sColorNo>
    
11.     </ColorSystem>
    
12.   </soap12:Body>
    
13. </soap12:Envelope>'
    
14. 
    
15. DECLARE @sResult NVARCHAR(MAX)
    
16. SELECT @sResult=dbo.fnpbWebServiceInvoke(@sUrl,N'',N'',@sSoapContent).value(N'(/Result[1])','NVARCHAR(MAX)')
    
17. SELECT @sResult=REPLACE(@sResult,N'"',N'')
    
18. SELECT @sResult=REPLACE(@sResult,N'{',N'')
    
19. SELECT @sResult=REPLACE(@sResult,N'}',N'')
    
20. SELECT @sResult=REPLACE(@sResult,N'[',N'')
    
21. SELECT @sResult=REPLACE(@sResult,N']',N'')

复制代码

1. Create PROCEDURE [dbo].[Proc_CallWebApi]
   
2.     -- Add the parameters for the stored procedure here
   
3.     @ApiUrl VARCHAR(200), --api请求地址
   
4.     @RequestType VARCHAR(5),--请求类型:POST,GET
   
5.     @ResponseText NVARCHAR(4000) OUTPUT
   
6. AS
   
7. BEGIN
   
8.     -- SET NOCOUNT ON added to prevent extra result sets from
   
9.     -- interfering with SELECT statements.
   
10.     SET NOCOUNT ON;
    
11. 
    
12.     -- Insert statements for procedure here
    
13.     DECLARE @data varchar(8000);
    
14.     --发送数据
    
15.     set @data=''
    
16. 
    
17.     Declare @Object as Int
    
18.     Exec sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @Object OUT
    
19.     Exec sp_OAMethod @Object, 'open', NULL, @RequestType,@ApiUrl,'false'
    
20.     IF @RequestType='POST'
    
21.     BEGIN
    
22.         Exec sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type','application/x-www-form-urlencoded'
    
23.     END
    
24.     Exec sp_OAMethod @Object, 'send', NULL, @data --发送数据
    
25.     Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
    
26.     --EXEC sp_OAGetErrorInfo @Object --异常输出
    
27.     --Select  @ResponseText as ResponseText
    
28.     --Select * from [dbo].[ParseJSON](@ResponseText)
    
29.     Exec sp_OADestroy @Object
    
30. 
    
31. END

复制代码

https://www.cnblogs.com/wangdongying/p/16903215.html