SQL Server使用IF EXISTS排除不存在的表和字段后仍然提示不存在表和字段。

Question

问题：我都已经用“IF EXISTS(SELECT TOP 1 1 FROM dbo.SYSCOLUMNS WHERE id=OBJECT_ID('PRJ_ORG_INFO') AND name='EADDRESS')”判断了PRJ_ORG_INFO表EADDRESS字段是否存在，为什么还是报错了。

我需要的解决方案：能够顺利执行下去。

1. 先查询各表各字段存在的状态

SELECT TOP 1 1 FROM dbo.SYSCOLUMNS WHERE id=OBJECT_ID('PERSON_ORG_INFO');
SELECT TOP 1 2 FROM dbo.SYSCOLUMNS WHERE id=OBJECT_ID('PERSON_ORG_INFO') AND name='EADDRESS';
SELECT TOP 1 3 FROM dbo.SYSCOLUMNS WHERE id=OBJECT_ID('PRJ_ORG_INFO');
SELECT TOP 1 4 FROM dbo.SYSCOLUMNS WHERE id=OBJECT_ID('PRJ_ORG_INFO') AND name='EADDRESS';

1. 操作一个表和字段都不存在的数据

IF EXISTS(SELECT TOP 1 1 FROM dbo.SYSCOLUMNS WHERE id=OBJECT_ID('PERSON_ORG_INFO') AND name='EADDRESS')
BEGIN
    UPDATE dbo.[PERSON_ORG_INFO] SET [EADDRESS] = '123' WHERE [EADDRESS] IS NOT NULL AND [EADDRESS] != '';
END

1. 操作一个表存在字段不存在的数据

IF EXISTS(SELECT TOP 1 1 FROM dbo.SYSCOLUMNS WHERE id=OBJECT_ID('PRJ_ORG_INFO') AND name='EADDRESS') 
BEGIN 
    UPDATE dbo.[PRJ_ORG_INFO] SET [EADDRESS] = '123' WHERE [EADDRESS] IS NOT NULL AND [EADDRESS] != ''; 
END

1. 操作一个表不存在字段不存在的数据

IF EXISTS(SELECT TOP 1 1 FROM dbo.SYSCOLUMNS WHERE id=OBJECT_ID('PRJ_ORG_INFO123456') AND name='EADDRESS') 
BEGIN 
    UPDATE dbo.[PRJ_ORG_INFO123456] SET [EADDRESS] = '123' WHERE [EADDRESS] IS NOT NULL AND [EADDRESS] != ''; 
END

Answer 1

这是预编译报的错误，使用动态SQL包裹起来。

EXEC sys.sp_executesql N'SQL';

EXEC sys.sp_executesql N'UPDATE dbo.[PERSON_ORG_INFO] SET [EADDRESS] = '123' WHERE [EADDRESS] IS NOT NULL AND [EADDRESS] != '';';