What is the difference between ; and GO in stored procedure in SQL Server?
What is the difference between ;
and GO
in stored procedure in SQL Server ?
Actually, if I have a stored procedure in SQL server and wanna to 开发者_如何学编程put t separate queries inside it which the first one just calculates number of records (count) and the second one selects some records based on some conditions, then what should I use between that two queries?
Go
or ;
;
just ends the statement.
GO is not a statement but a command to the server to commit the current batch to the Database. It creates a stop inside the transaction.
http://msdn.microsoft.com/en-us/library/ms188037.aspx
(Update, thanks for the comments):
GO is a statement intended for the Management studio as far as I know, maybe to other tools as well.
The semicolon separates queries, the GO command separates batches. (Also GO is not a T-SQL command, it's a command recognised by the sqlcmd and osql utilities and Management Studio.)
You can't use GO inside a stored procedure. If you would try, the definition of the procedure will end there, and the rest will be a separate batch.
A local variable has the scope of the batch, so after a GO command you can't use local variables declared before the GO command:
declare @test int
set @test = 42
GO
select @Test -- causes an error message as @Test is undefined
I know this thread is old but I thought these other uses/differences might be handy for other searches like myself regarding GO
.
Anything after the
GO
will not wind up in your sproc because theGO
will execute theCREATE/ALTER PROCEDURE
command. For example, if you run this...CREATE PROCEDURE X AS SELECT 1 As X GO SELECT 2 As X
Then after running it you go back in to edit the procedure you will find that only the SELECT 1 As X
is in there because the GO
created the sproc and anything after it is assumed to be the next thing you are doing and not part of the sproc.
- I'm surprised I haven't seen this mentioned much out there but the batch separator is not only specific to the program you are querying with but in the case of SSMS it is actually user editable! If I went into the settings and changed the batch separator from GO to XX then in my copy of SSMS, XX executes the batch not GO. So what would happen if I tried to execute a stored procedure that contained GO?
Think of GO
as a way of telling SSMS to send whatever is above it to the server for execution. The server never receives the GO
as that is just there to mark the end of a batch of command you want SSMS to send to the server.
If you have a scenario where you need to control execution flow in your stored procedure then you can use BEGIN TRANSACTION
and COMMIT TRANSACTION
for that and those are allowed in stored procedures.
GO is not a command to the server, it's the default batch separator for most of the client tools the MS supply. When the client tool encounters "GO" on a new line by itself, it sends whatever commands it has accumulated thus far to the server, and then starts over anew.
What this means is that any variables declared in one batch are not available in subsequent batches. And it also means that multi-line comments can't be placed around a "GO" command - because the server will see the first batch, and see an unterminated comment.
It marks the end of a batch in Query Analyzer and therefore signals the end of a stored procedure definition in that batch. As much as i know its not a part of sp. GO isn't a TSQL command.
And ; just ends the statement.
精彩评论