if object_id('dbo.F_CHECK_IF_FILE_EXISTS','FN') is not null
	begin drop function dbo.F_CHECK_IF_FILE_EXISTS end
go
create function dbo.F_CHECK_IF_FILE_EXISTS 
	( @FILEPATH nvarchar(500) )
returns int
as
/*
Function F_CHECK_IF_FILE_EXISTS returns a 1
if the file passed in parameter @FILEPATH exists,
and returns a 0 if is does not exist.

It works with UNC names if the SQL Server
service account has access to the share.
*/

begin

declare @retcode int
declare @FILE_EXISTS int

exec @retcode  = master.dbo.xp_fileexist 
	@FILEPATH,
	@FILE_EXISTS output 

return @FILE_EXISTS

end
go
print 'Check if file exists'

select [C:\autoexec.bat] = dbo.F_CHECK_IF_FILE_EXISTS('C:\autoexec.bat')

select [D:\autoexec.bat] = dbo.F_CHECK_IF_FILE_EXISTS('D:\autoexec.bat')

select [C:\autoexecx.bat] = dbo.F_CHECK_IF_FILE_EXISTS('C:\autoexecx.bat')

Check if file exists
C:\autoexec.bat 
--------------- 
1

(1 row(s) affected)

D:\autoexec.bat 
--------------- 
0

(1 row(s) affected)

C:\autoexecx.bat 
---------------- 
0

(1 row(s) affected)