json - 如何处理SQL中的错误并控制存储过程

Question

我从未学习过T-SQL中的错误陷阱。真是有时curl.xget

SELECT @JSON = curl.xget(null, ''+@GETSTRING+'') 

INSERT INTO [pcrd].[stg1_fredSeries]
           ([SeriesCode]
           ,[realStartTime]
           ,[realEndTime]
           ,[observationDate]
           ,[observationValue]
           ,[etl_dateUpdate]
           ,[ETL_UserUpdate]
           ,fk_series_id
           ,api_url)
    SELECT 
        @SERIES_CODE AS SeriesID,
        *,
        GETDATE() AS etl_dateUpdate,
        'sp_get_fredSeriesPCTpe4' AS etl_userUpdate,
        @api_type,
        @GETSTRING
    FROM
        OPENJSON(@JSON,'$.observations') 

引发错误:

Msg 6522, Level 16, State 1, Procedure pcrd.sp_get_fredSeriesPCType4, Line 72 [Batch Start Line 20]
A .NET Framework error occurred during execution of user-defined routine or aggregate "XGET":
System.Net.WebException: The remote server returned an error: (400) Bad Request.
System.Net.WebException:
at System.Net.WebClient.DownloadDataInternal(Uri address, WebRequest& request)
at System.Net.WebClient.DownloadString(Uri address)
at Curl.Get(SqlChars H, SqlChars url)

这只是问题还是引发错误并寻找Msg 6522？此插入和 @GETSTRING的构建位于游标提取中。游标提取中可以有BEGIN TRY吗？假设这是要走的路。

Answer 1

由于您要对curl.xget进行单个调用，因此可以在try/catch中捕获错误。

fetch next from thecursor into @GETSTRING;

--better to initialize @json on each iteration
select @JSON = null; 

begin try
    SELECT @JSON = curl.xget(null, ''+@GETSTRING+'') 
end try
begin catch
    --do something with the error, log etc...or just silence it
end catch

--when error for the @json assignment, @json will be null and no insertion happens
--because openjson returns no result
INSERT INTO [pcrd].[stg1_fredSeries]
SELECT 
FROM
    OPENJSON(@JSON,'$.observations')