sqlsever 针对返回值过长的api ,如何解析返回值

问题遇到的现象和发生背景

外部系统的接口的返回值 存储了 后端报错， 导致返回值过长，这边要求通过sql sever

INSERT INTO @xml(Content)
    EXEC @Result = sp_OAGetProperty @Status, 'responseText'

得到该 response, 但是要求从 response 的 status 做一层逻辑判断，现在没办法拆分，请各位指点一下
(ps: 不太想用程序集外挂代码实现);
返回值示例：

{
    "data":null,
    "errorCode":"DubboServiceLookup",
    "message":"kd.bos.isc.util.except.IscBizException: 
            单据反审核失败！可能存在下游数据。
            \nkd.bos.isc.util.except.IscBizException: 
            单据反审核失败！可能存在下游数据。
            \n\tat kd.bos.isc.util.script.feature.control.loop.Throw$1.eval(Throw.java:52)\n\tat kd.bos.isc.util.script.Util.eval(Util.java:116)\n\tat kd.bos.isc.util.script.statement.Block.eval(Block.java:43)\n\tat kd.bos.isc.util.script.statement.Block.eval(Block.java:36)\n\tat kd.bos.isc.util.script.Util.eval(Util.java:116)\n\tat kd.bos.isc.util.script.feature.control.decision.Decision.eval(Decision.java:47)\n\tat kd.bos.isc.util.script.Util.eval(Util.java:116)\n\tat kd.bos.isc.util.script.statement.Block.eval(Block.java:43)\n\tat kd.bos.isc.util.script.statement.Block.eval(Block.java:36)\n\tat kd.bos.isc.util.script.Util.eval(Util.java:116)\n\tat kd.bos.isc.util.script.parser.Program.eval(Program.java:65)\n\tat kd.bos.openapi.base.script.OpenApiScript.eval(OpenApiScript.java:130)\n\tat kd.bos.openapi.base.script.OpenApiScript.eval(OpenApiScript.java:122)\n\tat kd.bos.openapi.service.script.ScriptApiServiceImpl.execute(ScriptApiServiceImpl.java:35)\n\tat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.lang.reflect.Method.invoke(Method.java:498)\n\tat kd.bos.service.DispatchServiceImpl.invoke(DispatchServiceImpl.java:48)\n\tat com.alibaba.dubbo.common.bytecode.Wrapper1.invokeMethod(Wrapper1.java)\n\tat com.alibaba.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:46)\n\tat com.alibaba.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:72)\n\tat com.alibaba.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:53)\n\tat com.alibaba.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:66)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.RequestContextFilter.lambda$invokeProviderSide$0(RequestContextFilter.java:157)\n\tat kd.bos.mservice.rpc.dubbo.debug.DubboDebugUtil.invoke(DubboDebugUtil.java:46)\n\tat kd.bos.mservice.rpc.dubbo.filter.RequestContextFilter.invokeProviderSide(RequestContextFilter.java:148)\n\tat kd.bos.mservice.rpc.dubbo.filter.RequestContextFilter.invoke(RequestContextFilter.java:62)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:23)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:78)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.monitor.support.MonitorFilter.invoke(MonitorFilter.java:75)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.ProviderMetricFilter.invoke(ProviderMetricFilter.java:94)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.trace.ProviderSpanFilter.invoke(ProviderSpanFilter.java:84)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.circuitbreaker.filter.dubbo.CircuitbreakerFilter.lambda$invoke$0(CircuitbreakerFilter.java:34)\n\tat kd.bos.mservice.circuitbreaker.armor.ArmorCircuitbreaker.call(ArmorCircuitbreaker.java:102)\n\tat kd.bos.mservice.circuitbreaker.filter.dubbo.CircuitbreakerFilter.invoke(CircuitbreakerFilter.java:33)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.ThreadLifeCycleFilter.invoke(ThreadLifeCycleFilter.java:22)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:70)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:132)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:38)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:38)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:89)\n\tat com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:102)\n\tat com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:264)\n\tat com.alibaba.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:52)\n\tat com.alibaba.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:92)\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\n\tat java.lang.Thread.run(Thread.java:748)\n,TraceId：428c1cf5fdee720e   \nWebInstance：web-4650852774   \nTime:2022/09/09 10:16:58   \n====Stack====   \njava.lang.RuntimeException: kd.bos.isc.util.except.IscBizException: 单据反审核失败！可能存在下游数据。\nkd.bos.isc.util.except.IscBizException: 单据反审核失败！可能存在下游数据。\n\tat kd.bos.isc.util.script.feature.control.loop.Throw$1.eval(Throw.java:52)\n\tat kd.bos.isc.util.script.Util.eval(Util.java:116)\n\tat kd.bos.isc.util.script.statement.Block.eval(Block.java:43)\n\tat kd.bos.isc.util.script.statement.Block.eval(Block.java:36)\n\tat kd.bos.isc.util.script.Util.eval(Util.java:116)\n\tat kd.bos.isc.util.script.feature.control.decision.Decision.eval(Decision.java:47)\n\tat kd.bos.isc.util.script.Util.eval(Util.java:116)\n\tat kd.bos.isc.util.script.statement.Block.eval(Block.java:43)\n\tat kd.bos.isc.util.script.statement.Block.eval(Block.java:36)\n\tat kd.bos.isc.util.script.Util.eval(Util.java:116)\n\tat kd.bos.isc.util.script.parser.Program.eval(Program.java:65)\n\tat kd.bos.openapi.base.script.OpenApiScript.eval(OpenApiScript.java:130)\n\tat kd.bos.openapi.base.script.OpenApiScript.eval(OpenApiScript.java:122)\n\tat kd.bos.openapi.service.script.ScriptApiServiceImpl.execute(ScriptApiServiceImpl.java:35)\n\tat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.lang.reflect.Method.invoke(Method.java:498)\n\tat kd.bos.service.DispatchServiceImpl.invoke(DispatchServiceImpl.java:48)\n\tat com.alibaba.dubbo.common.bytecode.Wrapper1.invokeMethod(Wrapper1.java)\n\tat com.alibaba.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:46)\n\tat com.alibaba.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:72)\n\tat com.alibaba.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:53)\n\tat com.alibaba.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:66)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.RequestContextFilter.lambda$invokeProviderSide$0(RequestContextFilter.java:157)\n\tat kd.bos.mservice.rpc.dubbo.debug.DubboDebugUtil.invoke(DubboDebugUtil.java:46)\n\tat kd.bos.mservice.rpc.dubbo.filter.RequestContextFilter.invokeProviderSide(RequestContextFilter.java:148)\n\tat kd.bos.mservice.rpc.dubbo.filter.RequestContextFilter.invoke(RequestContextFilter.java:62)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:23)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:78)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.monitor.support.MonitorFilter.invoke(MonitorFilter.java:75)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.ProviderMetricFilter.invoke(ProviderMetricFilter.java:94)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.trace.ProviderSpanFilter.invoke(ProviderSpanFilter.java:84)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.circuitbreaker.filter.dubbo.CircuitbreakerFilter.lambda$invoke$0(CircuitbreakerFilter.java:34)\n\tat kd.bos.mservice.circuitbreaker.armor.ArmorCircuitbreaker.call(ArmorCircuitbreaker.java:102)\n\tat kd.bos.mservice.circuitbreaker.filter.dubbo.CircuitbreakerFilter.invoke(CircuitbreakerFilter.java:33)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.ThreadLifeCycleFilter.invoke(ThreadLifeCycleFilter.java:22)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:70)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:132)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:38)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:38)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:89)\n\tat com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:102)\n\tat com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:264)\n\tat com.alibaba.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:52)\n\tat com.alibaba.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:92)\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\n\tat java.lang.Thread.run(Thread.java:748)\n\n\tat com.alibaba.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:115)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.RequestContextFilter.lambda$invokeProviderSide$0(RequestContextFilter.java:157)\n\tat kd.bos.mservice.rpc.dubbo.debug.DubboDebugUtil.invoke(DubboDebugUtil.java:46)\n\tat kd.bos.mservice.rpc.dubbo.filter.RequestContextFilter.invokeProviderSide(RequestContextFilter.java:148)\n\tat kd.bos.mservice.rpc.dubbo.filter.RequestContextFilter.invoke(RequestContextFilter.java:62)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:23)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:78)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.monitor.support.MonitorFilter.invoke(MonitorFilter.java:75)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.ProviderMetricFilter.invoke(ProviderMetricFilter.java:94)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.trace.ProviderSpanFilter.invoke(ProviderSpanFilter.java:84)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.circuitbreaker.filter.dubbo.CircuitbreakerFilter.lambda$invoke$0(CircuitbreakerFilter.java:34)\n\tat kd.bos.mservice.circuitbreaker.armor.ArmorCircuitbreaker.call(ArmorCircuitbreaker.java:102)\n\tat kd.bos.mservice.circuitbreaker.filter.dubbo.CircuitbreakerFilter.invoke(CircuitbreakerFilter.java:33)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat kd.bos.mservice.rpc.dubbo.filter.ThreadLifeCycleFilter.invoke(ThreadLifeCycleFilter.java:22)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:70)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:132)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:38)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:38)\n\tat com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)\n\tat com.alibaba.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:89)\n\tat com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:102)\n\tat com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:264)\n\tat com.alibaba.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:52)\n\tat com.alibaba.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:92)\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\n\tat java.lang.Thread.run(Thread.java:748)\n",
    "status":false
}

substring 行不行

不知道你这个问题是否已经解决, 如果还没有解决的话:

• 这有个类似的问题, 你可以参考下: https://ask.csdn.net/questions/7749174
• 你也可以参考下这篇文章：SQLSEVER中的存储过程

如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 写成博客, 将相关链接放在评论区, 以帮助更多的人 ^-^

该问题已经解决，下面附上存储过程代码

```sql
CREATE PROCEDURE [dbo].[SP_Http_API]
    @httpType VARCHAR(8000) ,
    @contentType VARCHAR(8000) ,
    @authHeader VARCHAR(8000) ,
    @url VARCHAR(8000) ,
    @data VARCHAR(8000) ,
    @tokenName nvarchar(100),
    @tokenValue varchar(800),
    @resultConent xml output

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    DECLARE @Status INT;
    DECLARE @responseText VARCHAR(max);
    DECLARE @responseBody VARCHAR(max);

    DECLARE @Result int
    DECLARE @xml table(Content xml)



    EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @Status OUT;
    EXEC sp_OAMethod @Status, 'Open', NULL, 'POST', @url, 'false';

    IF ISNULL(@tokenName,'')<>'' AND ISNULL(@tokenValue,'')<>''
    BEGIN
        EXEC sp_OAMethod @Status, 'setRequestHeader', NULL, @tokenName,
            @tokenValue;
    END
    IF ISNULL(@authHeader,'')<>''
    BEGIN
        EXEC sp_OAMethod @Status, 'setRequestHeader', NULL, 'Authorization',
            @authHeader;
    END
    EXEC sp_OAMethod @Status, 'setRequestHeader', NULL, 'Content-type',
        'application/json';

    EXEC sp_OAMethod @Status, 'send', NULL, @data;


    INSERT INTO @xml(Content)
    EXEC @Result = sp_OAGetProperty @Status, 'responseText'--, @responseText OUT

    --DECLARE @resultConent xml;
    select top 1 @resultConent = Content  from @xml;

    CREATE TABLE #temp_table (n NVARCHAR(MAX))

    INSERT INTO #temp_table
    SELECT CONVERT(NVARCHAR(MAX),@resultConent)
    
    SELECT  len(n), n  FROM #temp_table tt;
    DROP TABLE #temp_table
END
GO