In SQL, the system stored procedure xp_fileexist is used to determine whether a file exists.

1. Call instructions for xp_fileexist

1,
Xp_fileexist "C: \ autoexec. Bat"

File exists file is a directory parent directory exists
-----------------------------------------------------
1 0 1
-- Table 1 exists, Table 0 does not exist
(1 row (s) affected)
------------------------------------------
2,
Declare @ result int
Exec xp_fileexist 'C: \ autoexec. bat', @ result output

@ Result = 1 The table file exists.
@ Result = 0 table file not saved

Ii. Test

If exists (select *
From sysobjects
Where type = 'U'
And
Name = 'temp _ xp_fileexist ')
Begin
Drop table temp_xp_fileexist
End

Create Table temp_xp_fileexist (a bit, B bit, C bit)
-- Declare @ SQL nvarchar (1000)
-- Set @ SQL = @ path + @ fname

Declare @ result int

-- The folder has 011 0
-- Insert into temp_xp_fileexist
-- Exec xp_fileexist 'C: \ Test'
-- Insert into temp_xp_fileexist
-- Exec xp_fileexist 'C: \ test', @ result output

-- The Folder does not exist 001 0
-- Insert into temp_xp_fileexist
-- Exec xp_fileexist 'C: \ tests'
-- Insert into temp_xp_fileexist
-- Exec xp_fileexist 'C: \ testss ', @ result output

-- Folder exists and file exists 101 1
-- Insert into temp_xp_fileexist
-- Exec xp_fileexist 'C: \ test \ test.txt'
-- Exec xp_fileexist 'C: \ test \ test.txt ', @ result output

-- Folder exists and file exists 000 0
Insert into temp_xp_fileexist
Exec master. DBO. xp_fileexist 'C: \ testsdf \ test.txt'
Exec xp_fileexist 'C: \ testsdf \ test.txt ', @ result output

-- Folder exists, file does not exist 001 0
-- Insert into temp_xp_fileexist
-- Exec xp_fileexist 'C: \ test \ testsdfs.txt'
-- Exec xp_fileexist 'C: \ test \ testsdfs.txt ', @ result output

Select * From temp_xp_fileexist

Select @ result

Drop table temp_xp_fileexist

Iii. Defining as a file judgment Function
Create Function DBO. fileexist (
@ Filepath nvarchar (600 ),
@ Filename nvarchar (400)
) Returns int
As
Begin

Declare @ result int
Declare @ SQL nvarchar (1000)
Set @ SQL = @ filepath + @ filename

Exec master. DBO. xp_fileexist @ SQL, @ result output
Return @ result
End