Instrukcja SQL IF jest ignorowana
Mam długi skrypt i lubię być w stanie uruchomić cały plik, gdy muszę i nie martwię się o to, czy części już zostały uruchomione. Ale poniższy skrypt daje mi problemy. Z jakiegoś powodu wychodzi poza instrukcję IF, nawet jeśli kolumny „EntityID” i „EntityType” nie istnieją, w którym to przypadku nie powinny przechodzić przez instrukcję IF. Czy ktoś może mi powiedzieć, co jest nie tak?
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
Skrypt do tabeli
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
Błędy:
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'.