iTyLdLwE

1USE [iConIRNet_SCU]
2GO
3
4/****** Object:  StoredProcedure [dbo].[Check_If_OldFilePath_Exists]    Script Date: 5/8/2019 10:28:31 AM ******/
5DROP PROCEDURE [dbo].[Check_If_OldFilePath_Exists]
6GO
7
8/****** Object:  StoredProcedure [dbo].[Check_If_OldFilePath_Exists]    Script Date: 5/8/2019 10:28:31 AM ******/
9SET ANSI_NULLS ON
10GO
11
12SET QUOTED_IDENTIFIER ON
13GO
14
15CREATE Proc [dbo].[Check_If_OldFilePath_Exists] as
16
17begin
18    set nocount on
19    create table #tempFileStatus(FilePath varchar(300),FileStatus varchar(30))
20    declare cur cursor local fast_forward for
21        (select FilePath from MigrationMissingImages where IsValid = 0)
22    open cur;
23    declare @fullpath varchar(250);
24    declare @isExists int;
25
26    fetch from cur into @fullpath
27    while @@FETCH_STATUS = 0
28        begin
29            exec xp_fileexist @fullpath, @isExists out
30            if @isExists = 1                
31                insert into #tempFileStatus values(@fullpath,'File exists')
32            else
33                insert into #tempFileStatus values(@fullpath,'File does not exists')
34            fetch from cur into @fullpath
35        end
36    close cur
37    deallocate cur
38    
39	update mi set isValid = 1
40	from #tempFileStatus 
41		join MigrationMissingImages mi on mi.FilePath = #tempFileStatus.FilePath
42		and  #tempFileStatus.FileStatus = 'File exists'
43    
44	drop table #tempFileStatus
45
46end
47GO