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

但是您仍然应该确保两列都在那里。