xp_fileexist with IF EXISTS

Question

I'm currently attempting to create a stored procedure that checks to see if a file is there, and if so it then runs some more code, if not. It stops.

So am using exec master..xp_fileexist @FileName

This returns

File Exists| File is a Directory| Parent Directory Exists
          1|                   0| 1

I can't figure out the syntax or set up for putting the stored proc into a IF EXISTS statement

IF EXISTS (
          exec master..xp_fileexist @FileName

          )

BEGIN 
select 'File is there'
END
ELSE
BEGIN
select 'File is not there'
END

The above doesn't work, can anyone put me on the right path?

Answer 1

xp_fileexists always returns a result.

So you don't want to check if the result exists, you want to check the results of the procedure call.

To get the results of a procedure call, you can use the insert... exec construct

declare @t table (fileexists bit, fileisdirectory bit, parentdirectoryexists bit)
insert @t 
exec master..xp_fileexist 'c:\windows\explorer.exe'

if (exists (select * from @t where fileexists=1))
begin
    select 'file is there'
end

Answer 2

you can do it like this:

declare @result as int
declare @path as nvarchar(50)

--set your path
set @path= 'C:\'

 EXEC master.dbo.xp_fileexist @path, @result OUTPUT

 if @result=1 
    print 'found'
 else
    print 'not found'