sql - 我们应该用 GO 语句结束存储过程吗？

Question

我们是否应该用 GO 语句结束存储过程，如果是的话，使用 GO 的优点是什么？

CREATE PROCEDURE uspGetAddress @City nvarchar(30)
AS
SELECT * 
FROM AdventureWorks.Person.Address
WHERE City = @City
GO

Answer 1

语句go，per the documentation

Signals the end of a batch of Transact-SQL statements to the SQL Server utilities.

...

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad-hoc session or script if this is the first GO.

A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.

Users must follow the rules for batches. For example, any execution of a stored procedure after the first statement in a batch must include the EXECUTE keyword. The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command.

存储过程定义，per the documentation for create procedure ，带有限制。它必须是批处理中的第一个(也是唯一一个)语句:

The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch.

这意味着存储过程的主体以批处理结束。在源文件中添加 GO 是一个很好的做法。特别是因为在创建存储过程之前和之后执行操作很常见。您经常会看到如下所示的源文件:

if (object_id('dbo.foobar') is not null ) drop procedure dbo.foobar
GO
-- dbo.foobar --------------------------------------------
-- 
-- This stored procedure does amazing and wonderful things
----------------------------------------------------------
create procedure dbo.foobar
as

   ...
   {a sequence of amazing and wonderful SQL statements}
   ...
   return 0
GO

grant execute on dbo.foobar to some_schema
GO

并且 GO 的值可以在 Sql Server Management Studio 的选项中进行调整。如果您想使用诸如 jump 之类的东西来代替 go，您可以(请记住，这样做几乎肯定会给自己带来痛苦。) .