IF 语句中间的 GO 命令 [英] GO commands in the middle of an IF statement

问题描述

我正在寻找关于创建表的 SO，仅当它们不存在于当前数据库中时(以便能够在可能已经或可能没有它们的不同数据库中创建它)并找到了这两个有用的主题

I was looking on SO about create tables only if they do not exist on the current DataBase (to be able to create it in different databases that MAY or MAY NOT have them already) and found those two helpful topics

• SQL Server:检查表是否存在
• 如何检查 SQL Server 中是否存在列表

所以我做了这个查询

IF (NOT EXISTS (SELECT * 
             FROM INFORMATION_SCHEMA.TABLES 
             WHERE TABLE_NAME = 'EMAILCONTAS'))
BEGIN
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[EMAILCONTAS](
    [NRSEQEMAILCONTAS] [numeric](8, 0) NOT NULL,
    [CDEMAILCONTAS] [varchar](40) NULL,
    [MSGEMAILCONTAS] [varchar](4000) NOT NULL,
    [CCOEMAIL] [varchar](100) NULL,
    [NRSEQOPERADORA] [numeric](8, 0) NOT NULL,
 CONSTRAINT [PK_EMAILCONTAS] PRIMARY KEY CLUSTERED 
(
    [NRSEQEMAILCONTAS] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[EMAILCONTAS]  WITH CHECK ADD FOREIGN KEY([NRSEQOPERADORA])
REFERENCES [dbo].[OPERADORA] ([NRSEQOPERADORA])
GO

ALTER TABLE [dbo].[EMAILCONTAS]  WITH CHECK ADD  CONSTRAINT [FK_EMAILCONTAS_OPERADORA] FOREIGN KEY([NRSEQOPERADORA])
REFERENCES [dbo].[OPERADORA] ([NRSEQOPERADORA])
GO

ALTER TABLE [dbo].[EMAILCONTAS] CHECK CONSTRAINT [FK_EMAILCONTAS_OPERADORA]
GO
END

但是当我执行它时，我在错误列表中得到了这个.

But when I execute it, I got this, in the error list.

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'ON'.

但它无论如何都会创建我的表(我在上面的代码之后放了一个Select * from PERSON;"，以检查错误是否会阻止下一个要编译的脚本.错误阻止了它.显示这个错误:

But it creates my table anyway (I put a "Select * from PERSON;" after the above code, to check if the error may block the next script to compile or not. And the error blocked it. Showing this error:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'Select'.

有办法避免吗?).当我执行这个查询并且表已经存在时，我收到了以下错误.

There is a way to avoid it?). And when I execute this query and the table already exist I got the follow errors.

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'ON'.
Msg 2714, Level 16, State 6, Line 2
There is already an object named 'EMAILCONTAS' in the database.
Msg 2714, Level 16, State 5, Line 2
There is already an object named 'FK_EMAILCONTAS_OPERADORA' in the database.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

<小时>

我怎样才能做到这一点而不会出现这些错误?有没有办法可以毫无问题地创建多个这样的代码?我做错了什么?

---

How could I accomplish this without getting those errors? Is there a way that I can create multiple code like this without problems? What am I doing wrong?

推荐答案

GO 类似于脚本的结尾；您可以从 BEGIN-END 语句中删除 GO.

GO is similar to the end of a script; you can remove the GOs from inside your BEGIN-END statement.

如果您绝对需要在代码中包含 GO，您可以将查询分解为:

If you absolutely need to have GO in the code, you can break up your query as:

IF (criteria)
BEGIN
    ...
END

GO

IF (newcriteria)
BEGIN
    ...
END

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