In this post we will review a function that we have used in another post. This SQL Server function will take in a full path and file as a string and will return a bit. It will return 1 if the file was found and 0 is it was not found.

Warning: If you use SQL Server to create a file that already exists, SQL Server will destroy that other file and create a new one, without giving you an error.

/*
Here is how you can use this function
=====================================
DECLARE @fileexists BIT;
DECLARE @filetotest VARCHAR(200);
SET @filetotest = 'c:\\newdir\\2.txt'
SET @fileexists = dbo.udf_FileExists(@filetotest);
IF @fileexists = 0
	BEGIN
		PRINT 'file not found'
	END
ELSE
	BEGIN
		PRINT 'found file'
	END
*/
CREATE FUNCTION [dbo].[udf_FileExists](@path varchar(8000))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result AS BIT)
END;
GO