如何使用xp_fileexist检查文件路径

I have the following code to update file path in sql server first i want to check if the file exist in the folder if it doesn't exist update otherwise skip. i used EXEC master.dbo.xp_fileexist @path, @result but i get

我有以下代码更新sql server中的文件路径我想检查文件夹中是否存在文件是否存在更新否则跳过。我使用EXEC master.dbo.xp_fileexist @path，@ result但我得到了

Msg 4104, Level 16, State 1, Line 10 The multi-part identifier "[WITESTCO].[dbo].[WIITEMX].[itemId]" could not be bound.

消息4104，级别16，状态1，行10多部分标识符“[WITESTCO]。[dbo]。[WIITEMX]。[itemId]”无法绑定。

declare @result as int 
declare @path as nvarchar(3000) 
set @path= '\\XY-SERVER\Data\PRODUCTION\VAULT\200000 - 399999 \PDF\'+[WITESTCO].[dbo].[WIITEMX].[itemId]+'.pdf' 
EXEC master.dbo.xp_fileexist @path, @result OUTPUT 

if @result=1 
 update [WITESTCO].[dbo].[WIITEMX] 
 set [docPath] = @path FROM [WITESTCO].[dbo].[WIITEMX]  where itemId LIKE'500%'
else  
 update [WITESTCO].[dbo].[WIITEMX] set [docPath] ='' FROM [WITESTCO].[dbo].[WIITEMX]  where itemId LIKE'500%'

1 个解决方案

#1

The update statement doesn't look right. Remove FROM [WITESTCO].[dbo].[WIITEMX] between the Set statement and the Where clause.

更新语句看起来不正确。从Set语句和Where子句之间删除FROM [WITESTCO]。[dbo]。[WIITEMX]。

  if @result=1 
     update [WITESTCO].[dbo].[WIITEMX] 
     set [docPath] = @path  
     where itemId LIKE'500%'
    else      
      update [WITESTCO].[dbo].[WIITEMX] 
      set [docPath] ='' 
         where itemId LIKE'500%'