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