SQL: Check if a File Exists

There may be a case where a SQL table contains a field that references a filename that is stored on the file system. The file could be an image (for example in the case of a web application that displays images) or another document that is linked from an application. To avoid errors it may be necessary to verify the existence of the file (i.e. the file exists). Fortunately, this is easily done with the xp_fileexist command. The xp_fileexist command returns an integer result that indicates if the file exists. The usage of the command is:
EXECUTE xp_fileexist [, OUTPUT]
The following script shows and example of looping through a set of records and validating, with xp_fileexist, that a file exists. If it doesn’t exist it updates the field with another value (if you are referencing images it could be a default image filename).
set nocount on;
set ansi_padding on;
set ansi_nulls on;
set quoted_identifier on;

-- declare variables.
declare @item as varchar(255)
declare @filename as varchar(255)
declare @filepath as varchar(255)
declare @cmd as varchar(1024)
declare @result as int

-- declare a cursor for storing the records in the table.
declare items insensitive scroll cursor
for
	select
		[Item]
		,[File Name]
		,[File Path]
	from  [Table1]
	order by 
		[File Name]

-- open the cursor.
open items

-- loop through all the indexes.
while (1=1)
	begin
		fetch next from items into @item, @filename, @filepath;
		if @@fetch_status <> 0 break;
		
		set @filepath = @filepath + @filename
		exec master.dbo.xp_fileexist @filepath, @result OUTPUT
		if (@result = 0) -- image does not exist
			begin
				--print (@result)			
				-- Update the record to point to a generic image
				set @cmd = 'UPDATE [Table1] SET [File Name] = ''NA.jpg'' WHERE [Item] = ''' + @item + ''''
				-- print (@cmd)
				exec (@cmd)
			end
	end

-- close cursor
close items;
deallocate items;

go

Leave a Comment