How can i check file existence

Question

I have the following code to update docPath field, I need help on how to check the existence of a file before running the update statement or update only if the file exist in the directory otherwise skip

INSERT INTO DocPath(itemId,docPath)
SELECT 
  itemId,
  '\\ABC-SERVER\Data\PRODUCTION\VAULT\2500000 - 2599999 \PDF\''
   +[WITESTCO].[dbo].[WIITEMX].[itemId]
   +'.pdf'  as docPath
   FROM [WITESTCO].[dbo].[WIITEMX]
   WHERE 
      itemId like '2500%' or itemId like '2501%' ;

Possible duplicate of [Check for file exists or not in sql server?](https://stackoverflow.com/questions/11740000/check-for-file-exists-or-not-in-sql-server) — Alberto Martinez, Oct 11 '17 at 01:04

score 0 · Answer 1 · answered Oct 10 '17 at 21:07

0

Create a function like this

CREATE FUNCTION dbo.fn_FileExists(@path varchar(512))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

And use the function like this

IF dbo.fn_FileExists('MyPath') = 1
INSERT INTO DocPath(itemId,docPath)
SELECT itemId,'MyPath' ...;

Similar to this question

answered Oct 10 '17 at 21:07

Artashes Khachatryan

387
2
10

Isn't it possible that the file could be removed after the function is called but before the `INSERT` statement is processed? – STLDev Oct 10 '17 at 21:10
Of course it is possible, but as I know it is impossible to lock the file to being removed from the database transaction. – Artashes Khachatryan Oct 10 '17 at 21:11
In that case, the original problem can still exist. Would it not be better in this case to handle the error? – STLDev Oct 10 '17 at 21:13
The same thing can happen even after the insert statement completes. For example we check the existence of a file, it exists, we inserted a row, then someone deletes that file (lets say after 5 mins). – Artashes Khachatryan Oct 10 '17 at 21:15

How can i check file existence

1 Answers1