USE [nodedb_003]

GO

/****** Object:  UserDefinedFunction [dbo].[GetWeb]    Script Date: 07/20/2023 21:26:16 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[GetWeb]

( 

    @url nvarchar(100),

    @data nvarchar(500)

)

RETURNS nvarchar(4000) #特别主要有些字符串尽量定义好长度

AS

BEGIN

DECLARE @ErrMsg VARCHAR(5000)

DECLARE @token INT,@result INT,@returnTextErr VARCHAR(200),@HttpStatus VARCHAR(200)       

Declare @ResponseText AS  varchar(4000)

DECLARE @source varchar(max)

DECLARE @description varchar(max)

SET @ResponseText=''

EXECUTE @result =sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @token OUT

IF @result<>0

BEGIN

        EXECUTE sp_OAGetErrorInfo @token, @ErrMsg OUT, @returnTextErr OUT

        SET @ErrMsg = '初始化对象失败，' + @ErrMsg + ISNULL(@returnTextErr, '')

        RETURN @ErrMsg

END 

EXECUTE @result = sp_OAMethod @token, 'open', NULL, 'GET',@url,'false'

IF @result <> 0

BEGIN

        EXECUTE sp_OAGetErrorInfo @token, @ErrMsg OUT, @returnTextErr OUT

        SET @ErrMsg = '创建连接失败，' + @ErrMsg + ISNULL(@returnTextErr, '')

        RETURN @ErrMsg

END 

EXECUTE @result=sp_OAMethod @token, 'send', NULL, @data --发送数据

IF @result <> 0

BEGIN

        EXECUTE sp_OAGetErrorInfo @token, @ErrMsg OUT, @returnTextErr OUT

        SET @ErrMsg = '发送请求头失败，'+ @ErrMsg + ISNULL(@returnTextErr, '')

        RETURN @ErrMsg

END

EXECUTE @result = sys.sp_OAGetProperty @token, 'Status', @HttpStatus OUT   

IF @result <> 0

BEGIN

         EXECUTE sp_OAGetErrorInfo @token, @ErrMsg OUT, @returnTextErr OUT

        SET @ErrMsg = '读取[Status]属性值失败，'+ @ErrMsg + ISNULL(@returnTextErr,'')

        RETURN @ErrMsg

END

 IF @HttpStatus <> 200

 BEGIN

       SET @ErrMsg ='访问错误,http状态代码,'+ @HttpStatus +''

       RETURN @ErrMsg

 END

--EXECUTE @ResponseText =sp_OAGetProperty  @token, 'responseText'

--set @ResponseText='1111'

--Exec sp_OAMethod @token, 'responseText',@ResponseText OUT

EXEC @result= SP_OAGetProperty @token,'responseText',@ResponseText OUT;

IF @result <> 0

BEGIN

         EXECUTE sp_OAGetErrorInfo @token, @ErrMsg OUT, @returnTextErr OUT

        SET @ErrMsg = '读取[responseText]属性值失败，'+ @ErrMsg + ISNULL(@returnTextErr,'')

        EXEC sp_OADestroy @token

        RETURN @ErrMsg

END

--EXECUTE sp_OAGetErrorInfo @token,@source OUT, @description OUT --异常输出

--Select  @ResponseText 

EXEC sp_OADestroy @token

RETURN @ResponseText

END