It works fine at my place
CREATE FUNCTION [dbo].[IsExists](@FilePath nvarchar(255))
RETURNS int
AS
BEGIN
DECLARE @File_Exists INT;
EXEC Master.dbo.xp_fileexist @FilePath, @File_Exists OUT;
RETURN @File_Exists
END
GO
DECLARE @FilePath VARCHAR(255)= 'F:\data.txt'
SELECT [dbo].[IsExists] (@FilePath) AS FileExists
GO
FileExists
1
Running without using function
DECLARE @FilePath VARCHAR(255)= 'F:\data.txt'
CREATE TABLE #FileExists (isExists int, isDirectory int, isParentDirExists int);
INSERT INTO #FileExists (isExists, isDirectory, isParentDirExists)
EXEC Master.dbo.xp_fileexist @FilePath;
SELECT * FROM #FileExists
DROP TABLE #FileExists;
isExists isDirectory isParentDirExists
1 0 1
I Just have changed Nvarchar(Max) to Nvarchar(255)