SQL IF语句被忽略 [英] SQL IF statement is being ignored

问题描述

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

I have a long script and I like to be able to just run the whole file when I need to and not worry about if parts of it have already ran. But the script below is giving me problems. For some reason it is getting past the IF statement even when the columns 'EntityID' and 'EntityType' do not exist, in which case it should not get through the IF statement. Can someone tell me what is wrong?

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'.

推荐答案

马丁肯定会做点什么。解析器在解析时正在处理 IF 中的内容，并忽略您的 IF 是否会平移。这与你不能做的原因相同：

Martin was definitely onto something. The stuff inside the IF is being treated by the parser at parse time and ignoring whether your IF will pan out. This is the same reason you can't do:

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

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

One workaround would be to use dynamic 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

但你仍然应该确定两个列在那里。

But you still should be sure that both columns are there.

这篇关于SQL IF语句被忽略的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！