使用OPENROWSET查询不存在路径时，如何返回空结果而非13807错误？

问题场景 #

你的查询语句如下：

SELECT *
FROM  OPENROWSET
(
    BULK 'mytestpath/*/*',
    DATA_SOURCE = 'LocalDataLake',
    FORMAT = 'Parquet'
)
WITH(
Foo INT,
Bar INT
) X

当mytestpath/*/*路径下无文件或路径不存在时，查询会抛出错误：

Content of directory on path 'mytestpath/*/*' cannot be listed.

解决方案 #

方法一：先检查文件存在性再执行查询

利用sys.fn_get_remote_files函数检查指定路径下是否有文件，存在则执行原查询，否则返回结构一致的空结果集：

IF EXISTS (
    SELECT 1
    FROM sys.fn_get_remote_files(
        DATA_SOURCE_NAME = 'LocalDataLake',
        PATH = 'mytestpath/*/*'
    )
)
BEGIN
    SELECT *
    FROM  OPENROWSET
    (
        BULK 'mytestpath/*/*',
        DATA_SOURCE = 'LocalDataLake',
        FORMAT = 'Parquet'
    )
    WITH(
    Foo INT,
    Bar INT
    ) X
END
ELSE
BEGIN
    -- 返回与原查询结构匹配的空结果
    SELECT CAST(NULL AS INT) AS Foo, CAST(NULL AS INT) AS Bar
    WHERE 1 = 0
END

方法二：使用TRY_OPENROWSET简化逻辑

如果你的Synapse SQL Serverless环境支持TRY_OPENROWSET语法，可直接替换OPENROWSET，它在遇到路径无文件等错误时会返回空结果集而非报错：

SELECT *
FROM TRY_OPENROWSET
(
    BULK 'mytestpath/*/*',
    DATA_SOURCE = 'LocalDataLake',
    FORMAT = 'Parquet'
)
WITH(
Foo INT,
Bar INT
) X

注意：TRY_OPENROWSET是较新版本Synapse SQL Serverless支持的特性，若环境不兼容，建议使用方法一。

内容的提问来源于stack exchange，提问作者Martin Smith