How to check a file exists in transact sql using xp_FileExists

 

exec sp_configure 'show advanced options',1
go
reconfigure;
go
exec sp_configure 'xp_cmdshell', 1;
go
reconfigure;
go

drop function sp_FileExists

if object_id('sp_FileExists') is null
begin
 declare @sql nvarchar(2000)
 set @sql='
 create function dbo.sp_FileExists(@fn nvarchar(1000)) returns bit as
 begin
 declare @result int
 exec master.dbo.xp_fileexist @fn, @result output
 if @result = 1 return 1
 return 0 
 end
 '
 exec @sql
end

select DocumentId, 'd:\MyStaffFile_Files\media\' + DocumentPath 
,case when DocumentPath is null then 'no path'
 when dbo.sp_FileExists('d:\MyStaffFile_Files\media\' + DocumentPath) = 1 then 'exists'
 else 'does not exist'
 end
from Documents