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