Vishal Monpara's Blog

CREATE FUNCTION [dbo].[fn_FileExists] (@FileName nvarchar(255))
RETURNS bit AS
BEGIN
	DECLARE @FS int
	DECLARE @FileID int
	DECLARE @OLEResult int
	declare @Exists bit

	EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
	IF @OLEResult <> 0
	BEGIN
		RETURN 0
	END

	EXECUTE @OLEResult = sp_OAMethod @FS, 'FileExists', @Exists OUT, @FileName

	IF @OLEResult <> 0
	BEGIN
		return 0
	END
	EXECUTE @OLEResult = sp_OADestroy @FS

	return @Exists
END

Important things to remember.

1. Existence of the file will be checked on the server in which it is created. If you are running this stored procedure on remote machine, it will check for file on remote machine and not on local machine.
2. To call a function, you should write

   if dbo.fn_FileExists ('Filename.txt') = 1
   	print 'exist'

   Here “dbo.” must be appended to call the function, otherwise it will give error.