Se está ignorando la instrucción SQL IF
Tengo un script largo y me gusta poder ejecutar todo el archivo cuando lo necesito y no preocuparme por si ya se han ejecutado partes. Pero el guión de abajo me está dando problemas. Por alguna razón, está pasando la declaración IF incluso cuando las columnas 'EntityID' y 'EntityType' no existen, en cuyo caso no debería pasar por la declaración IF. ¿Puede alguien decirme qué está mal?
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
El script para la mesa.
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
Los errores:
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'.