SQL stored procedures with wildcards to find files in the specified directory

The process by which wildcards find files in a specified directory:

-find the files in the specified folder (directory). ALTER PROCEDURE [FileS]. [DIR_FileS] @ Path nvarchar (1000)-search path. -- indicates that [folder path] must end with'\';-- indicates that [file path] cannot end with\. (the file name can be wildcard), @ FileS nvarchar (MAX) OUTPUT-returns the string of file names found, @ Depth int=1-- searches for the depth of the [folder]. If not 1, [subfolder] will be searched. 0 all directories, 1 level 1 directory, 2 level 2 directory, and so on, @ FG nvarchar (10) = NULL-- the delimiter of the filename string; default char (13) ASBEGIN SET NOCOUNT ON; SET @ FG=ISNULL (@ FG,char (13));-- delimiter The default char (13) IF @ Depth0 AND SUBSTRING (@ Path,@i,1)'\ 'BEGIN--' SET @ iConstructive1; END; IF @ I > 0 BEGIN-intercepts the filename SET @ FileName=SUBSTRING (@ Path,@i+1,LEN (@ Path)) SET @ Path = SUBSTRING (@ Path,1,@i-1);-- convert wildcard SET @ FileName=replace (@ FileName,'*','%'); SET @ FileName=replace (@ FileName,'?','_'); END; END -- print'@ Path='+ISNULL (@ Path,'') +'@ FileName='+ISNULL (@ FileName,''); insert @ Tab exec master..xp_dirtree @ Path, @ Depth-- search depth: 0 all directories, 1 first level directory, 2 second level directory, and so on, 1 -- 0 folder, non-0: folder and file name-- Delete mismatch records IF ISNULL (@ FileName,'')''BEGIN-- select [FileName] from @ Tab WHERE [isfile] = 1 AND [FileName] like @ FileName; DELETE @ Tab WHERE [isfile] 1 OR [FileName] not like @ FileName; END ELSE DELETE @ Tab WHERE [isfile] 1;-- assemble the file name string Add the delimiter @ FG SET @ FileS=''; SELECT @ FileS=@FileS+@FG+ISNULL ([FileName],'') FROM @ Tab; IF @ FileS''BEGIN-- remove the first separator @ FG IF SUBSTRING (@ FileS,1,LEN (@ FG)) = @ FG BEGIN; SET @ FileS=SUBSTRING (@ FileS,LEN (@ FG) + 1M Len (@ FileS)) END; END; END1:-- print'@ FileS='+@FileS;-- select [FileName] from @ Tab;END