drop table #XpFileExists
CREATE TABLE #XpFileExists
(
IsFileExists bit,
IsFileDirectory bit,
IsParentDirectoryExists bit
)
drop table #Path
CREATE TABLE #Path
(
[FilePath] [varchar](100) NOT NULL,
[IsFileExists] [bit] NULL,
[IsFileDirectory] [bit]
NULL,
[IsParentDirectoryExists] [bit]
NULL,
[IsActive] [bit] NOT NULL,
[UpdatedOn] [datetime]
NULL,
[CreatedOn] [datetime]
NOT NULL,
)
declare @FilePath [varchar](500)
declare @IsFileExists bit
declare @IsFileDirectory bit
declare @IsParentDirectoryExists
bit
declare @FileCounter table (FilePath varchar(500))
insert into @FileCounter(FilePath)
values
('C:\Share\'),
('D:\Share')
while exists(select top 1 * from @FileCounter)
begin
select top 1 @FilePath = FilePath from @FileCounter
delete from #XpFileExists
insert into #XpFileExists
Exec master.dbo.xp_fileexist @FilePath
select
@IsFileExists = IsFileExists,
@IsFileDirectory = IsFileDirectory,
@IsParentDirectoryExists =
IsParentDirectoryExists
from #XpFileExists
select * from #XpFileExists
update b set
IsFileExists = @IsFileExists,
IsFileDirectory = @IsFileDirectory,
IsParentDirectoryExists =
@IsParentDirectoryExists,
UpdatedOn = GETDATE()
from #Path b
where b.FilePath = @FilePath
delete from @FileCounter where FilePath = @FilePath
end
select *
from #Path
where IsFileDirectory = 0
and IsActive = 1
