SQL Server 2017存储过程实现指定文件存在性检查及路径返回

Let's refine your existing code to add the file existence check and return the required results. First, we'll fix a couple of small issues in your current code (like the hardcoded @SearchFile value and unpopulated fullpath column) then build out the missing logic.

Full Modified Stored Procedure #

CREATE PROCEDURE SearchFile_InAllDirectories 
    @SearchFile VARCHAR(100) 
AS
BEGIN
    SET NOCOUNT ON; -- Prevent extra result sets from interfering with output

    -- Declare variables
    DECLARE @BasePath VARCHAR(1000), 
            @FullPath VARCHAR(2000); 

    -- Clean up temp tables if they exist (avoids errors on repeated runs)
    IF OBJECT_ID('tempdb..#tmp_BasePath') IS NOT NULL DROP TABLE #tmp_BasePath;
    CREATE TABLE #tmp_BasePath ( basePath VARCHAR(1000) ); 

    IF OBJECT_ID('tempdb..#tmp_DirectoryTree') IS NOT NULL DROP TABLE #tmp_DirectoryTree;
    CREATE TABLE #tmp_DirectoryTree ( 
        id INT IDENTITY(1,1), 
        subdirectory VARCHAR(512), 
        depth INT, 
        isfile BIT, 
        fullpath VARCHAR(2000) -- Increased length to handle longer paths
    ); 

    -- Populate hardcoded base paths (keep your list here)
    INSERT INTO #tmp_BasePath (basePath) 
    VALUES ('\\Path1'), ('\\Path1\Images_5'), ('\\Path3\Images_4'), ('\\basketballfolder\2017_Images'), ('\\basketballfolder\2017_Images');

    -- Cursor to iterate through each base path
    DECLARE basePath_results CURSOR FOR 
        SELECT bp.basePath FROM #tmp_BasePath bp;

    OPEN basePath_results;
    FETCH NEXT FROM basePath_results into @BasePath;

    WHILE @@FETCH_STATUS = 0 
    BEGIN 
        -- Pull directory tree for current base path
        INSERT INTO #tmp_DirectoryTree (subdirectory, depth, isfile) 
        EXEC master.sys.xp_dirtree @BasePath, 0, 1; 

        -- Update fullpath with complete file/folder path (handle trailing backslashes)
        SET @BasePath = CASE WHEN RIGHT(@BasePath, 1) = '\' THEN @BasePath ELSE @BasePath + '\' END;
        
        UPDATE #tmp_DirectoryTree
        SET fullpath = @BasePath + subdirectory
        WHERE fullpath IS NULL; -- Target only the rows we just inserted

        FETCH NEXT FROM basePath_results INTO @BasePath;
    END 

    CLOSE basePath_results; 
    DEALLOCATE basePath_results; 

    -- Check for target file and return results
    SELECT 
        1 AS FileExists, -- 1 = file found
        fullpath AS FullFilePath
    FROM #tmp_DirectoryTree
    WHERE isfile = 1 -- Only check actual files, not folders
        AND subdirectory = @SearchFile -- Match exact filename
    UNION ALL
    -- Return a "not found" row if no matches exist
    SELECT 0 AS FileExists, NULL AS FullFilePath
    WHERE NOT EXISTS (
        SELECT 1 FROM #tmp_DirectoryTree
        WHERE isfile = 1 AND subdirectory = @SearchFile
    );

    -- Clean up temporary tables
    DROP TABLE #tmp_BasePath;
    DROP TABLE #tmp_DirectoryTree;
END

Key Improvements & Explanations #

• Temp Table Cleanup: Added checks to drop temp tables if they exist, preventing errors when running the procedure multiple times in the same session.
• Populated fullpath Column: The xp_dirtree returns relative paths, so we concatenate them with the base path to get the full file location. We also handle trailing backslashes to avoid invalid paths like \\Path1test2019.txt.
• Removed Hardcoded @SearchFile: Now the procedure uses the input parameter as intended, instead of overwriting it with a fixed value.
• Clear Result Output: We return a FileExists flag (1 = found, 0 = not found) and the full path of each matching file. If no matches exist, we explicitly return a row indicating the file wasn't found.
• SET NOCOUNT ON: Suppresses "rows affected" messages, which is standard practice for stored procedures to keep output clean.

Quick Notes #

• Permissions: Make sure the SQL Server service account has read access to all network paths you're scanning.
• Duplicate Base Paths: Your sample includes duplicate base paths (\\basketballfolder\2017_Images twice) – you can add DISTINCT to the INSERT statement to avoid redundant directory scans.
• Long Paths: If you need to support paths over 260 characters, enable long path support in Windows and switch path columns to VARCHAR(MAX).

内容的提问来源于stack exchange，提问作者Bakari Lewis