IF 是否以任何一种方式执行？

Question

我在执行以下语句时遇到错误：

 /* AccountTypes Constraints */
 IF  EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AccountTypes]') AND type in (N'U'))
 BEGIN
  PRINT 'Table [AccountTypes] exist.'

  IF NOT EXISTS(SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Accounts_AccountTypes]') AND parent_object_id = OBJECT_ID(N'[dbo].[Accounts]'))
  BEGIN
   ALTER TABLE [dbo].[Accounts]  WITH NOCHECK ADD  CONSTRAINT [FK_Accounts_AccountTypes] FOREIGN KEY([AccountType])
   REFERENCES [dbo].[AccountTypes] ([AccountTypeID])
   GO
   ALTER TABLE [dbo].[Accounts] CHECK CONSTRAINT [FK_Accounts_AccountTypes]
   GO 
  END
 END
 ELSE
  PRINT 'Table [AccountTypes] Does not exist to create [FK_Accounts_AccountTypes].'
 /* END: AccountTypes Constraints */

情况是表 [AccountTypes] 确实不存在，但是为什么当我已经检查表是否存在时我会收到错误！！！！

以下是我收到的错误：

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'AccountTypeID'.

Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_Accounts_AccountTypes' does not exist.

Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'END'.

SQL 2005 Express

Answer 1

ALTER TABLE 必须是批处理中的第一个。
也就是说，你不能首先以你所拥有的形式来测试存在。

那么你就不能中途把 GO 作为批次分隔符了。

所以，动态 SQL：

 /* AccountTypes Constraints */
 IF  EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AccountTypes]') AND type in (N'U'))
 BEGIN
  PRINT 'Table [AccountTypes] exist.'

  IF NOT EXISTS(SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Accounts_AccountTypes]') AND parent_object_id = OBJECT_ID(N'[dbo].[Accounts]'))
  BEGIN
   EXEC ('ALTER TABLE [dbo].[Accounts]  WITH NOCHECK ADD  CONSTRAINT [FK_Accounts_AccountTypes] FOREIGN KEY([AccountType])
   REFERENCES [dbo].[AccountTypes] ([AccountTypeID])')
   EXEC ('...')