SQL Server cursor for file exists check

Question

I have a table which have the details including file_path, datafilename etc.

The complete file path is the combination of file_path and datafilename. I need to create a cursor to check the file exists or not..

If the file exists, I need to update the flag column with Y else N.

Please help me.

Answer 1

I have incorporated the reference provided in comment (Check for file exists or not in sql server?) in cursor, please use your table name

DECLARE @file_path nvarchar(200) ,@datafilename nvarchar(200)  

DECLARE FileExist_cursor CURSOR FOR     
SELECT file_path,datafilename  
FROM table  

OPEN FileExist_cursor    

FETCH NEXT FROM FileExist_cursor     
INTO @file_path,@datafilename    

WHILE @@FETCH_STATUS = 0    
BEGIN    

 DECLARE @result INT
 DECLARE @path NVARCHAR(400)=@file_path+@datafilename

 exec  master.dbo.xp_fileexist @path,@result OUTPUT

 update  table
 set flag = case when @result='1' then 'Y' else 'N' end
 WHERE 
 file_path = @file_path
 and datafilename    = @datafilename
  
 FETCH NEXT FROM FileExist_cursor     
 INTO @file_path,@datafilename    

END     
CLOSE FileExist_cursor;    
DEALLOCATE FileExist_cursor;