5NZqev51

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