SQL IF语句被忽略

Question

我有一个很长的脚本，我希望能够在需要时运行整个文件，而不必担心它的部分是否已经运行。但是下面的脚本给我带来了问题。由于某些原因，即使列'EntityID'和'EntityType'不存在，它也会越过IF语句，在这种情况下，它不应该通过IF语句。有人能告诉我什么是错的吗？

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Notes' AND COLUMN_NAME IN ('EntityId', 'EntityType')) 
BEGIN 
    BEGIN TRANSACTION 
     --Delete notes where EntityType and EntityID are both NULL 
     DELETE FROM [dbo].[Notes] 
     WHERE [EntityId] = NULL 
     AND [EntityType] = NULL 
     --Delete notes where the corresponding contact or account has been deleted. 
     OR [ID] IN (9788, 10684, 10393, 10718, 10719) 

     --Populate new columns with all existing data 
     UPDATE [dbo].[Notes] 
     SET [AccountId] = [EntityId] 
     WHERE [EntityType] = 1 

     UPDATE [dbo].[Notes] 
     SET [ContactId] = [EntityId] 
     WHERE [EntityType] = 2 

     --Delete EntityId and EntityType columns from the Notes table 
     ALTER TABLE [dbo].[Notes] 
     DROP COLUMN [EntityId], [EntityType] 
    COMMIT 
END 
GO 

的脚本表

CREATE TABLE [dbo].[Notes](
    [ID] [int] IDENTITY(1,1) NOT NULL, 
    [AnnotationID] [uniqueidentifier] NULL, 
    [CreatedBy] [int] NULL, 
    [CreatedDate] [datetime] NULL, 
    [NoteText] [ntext] NULL, 
    [OriginalAnnotationID] [uniqueidentifier] NULL, 
    [Active] [bit] NULL, 
    [ContactId] [int] NULL, 
    [AccountId] [int] NULL, 
CONSTRAINT [PK_Notes] PRIMARY KEY CLUSTERED 
(
    [ID] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 

GO 

ALTER TABLE [dbo].[Notes] WITH CHECK ADD CONSTRAINT [FK_Account_ID] FOREIGN KEY([AccountId]) 
REFERENCES [dbo].[Account] ([ID]) 
GO 

ALTER TABLE [dbo].[Notes] CHECK CONSTRAINT [FK_Account_ID] 
GO 

ALTER TABLE [dbo].[Notes] WITH CHECK ADD CONSTRAINT [FK_ContactId_ID] FOREIGN KEY([ContactId]) 
REFERENCES [dbo].[Contact] ([ID]) 
GO 

ALTER TABLE [dbo].[Notes] CHECK CONSTRAINT [FK_ContactId_ID] 
GO 

的错误：

Msg 207, Level 16, State 1, Line 6 
Invalid column name 'EntityId'. 
Msg 207, Level 16, State 1, Line 7 
Invalid column name 'EntityType'. 
Msg 207, Level 16, State 1, Line 12 
Invalid column name 'EntityType'. 
Msg 207, Level 16, State 1, Line 16 
Invalid column name 'EntityType'. 

Answer 1

马丁绝对是一些东西。解析器在解析时正在处理IF中的内容，并忽略您的IF是否会被移出。这是你不能做同样的原因：

IF 1 = 1 
    CREATE TABLE #x(a INT); 
ELSE 
    CREATE TABLE #x(b INT); 

一种解决方法是使用动态SQL：

IF EXISTS ... 
BEGIN 
    BEGIN TRANSACTION; 

    DECLARE @sql NVARCHAR(MAX); 

    SET @sql = N' 
     DELETE FROM [dbo].[Notes] 
     WHERE [EntityId] IS NULL 
     AND [EntityType] IS NULL 
     --Delete notes where the corresponding contact or account has been deleted. 
     OR [ID] IN (9788, 10684, 10393, 10718, 10719) 

     --Populate new columns with all existing data 
     UPDATE [dbo].[Notes] 
     SET [AccountId] = [EntityId] 
     WHERE [EntityType] = 1 

     UPDATE [dbo].[Notes] 
     SET [ContactId] = [EntityId] 
     WHERE [EntityType] = 2 

     --Delete EntityId and EntityType columns from the Notes table 
     ALTER TABLE [dbo].[Notes] 
     DROP COLUMN [EntityId], [EntityType]'; 

    EXEC sp_executesql @sql; 

    COMMIT TRANSACTION; 
END 

但你还是应该确保两个列在那里。

Answer 2

我怀疑的问题是，其中一列存在，但不能同时使用。请尝试以下操作：

IF 2 = (SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Notes' AND COLUMN_NAME IN ('EntityId', 'EntityType'))