Confusion with the GO statement, uncommitted transactions and alter procedure

I would like to get to the bottom of this because it's confusing me. Can anyone explain when I should use the GO statement in my scripts?

As I understand it the GO statement is not part of the T-SQL language, instead it is used to send a batch of statements to SQL server for processing.

When I run the following script in Query Analyser it appears to run fine. Then I close the window and it displays a warning:

"There are uncommitted transactions. Do you wish to commit these transactions before closing the window?"

BEGIN TRANSACTION;
GO 

ALTER PROCEDURE [dbo].[pvd_sp_job_xxx]
    @jobNum varchar(255)

AS
BEGIN

    SET NOCOUNT ON;
    UPDATE tbl_ho_job SET delete='Y' WHERE job = @job;
END


COMMIT TRANSACTION;
GO

However if I add a GO at the end of the ALTER statement it is OK (as below). How come?

BEGIN TRANSACTION;
GO



ALTER PROCEDURE [dbo].[pvd_sp_xxx]
    @jobNum varchar(255)

AS
BEGIN

    SET NOCOUNT ON;
    UPDATE tbl_ho_job SET delete='Y' WHERE job = @job;
END
GO


COMMIT TRANSACTION;
GO

I thought about removing all of the GO's but the开发者_开发技巧n it complains that the alter procedure statement must be the first statement inside a query batch? Is this just a requirement that I must adhere to?

It seems odd because if I BEGIN TRANSACTION and GO....that statement is sent to the server for processing and I begin a transaction.

Next comes the ALTER procedure, a COMMIT TRANSACTION and a GO (thus sending those statements to the server for processing with a commit to complete the transaction started earlier), how come it complains when I close the window still? Surely I have satisfied that the alter procedure statement is the first in the batch. How come it complains about are uncommitted transactions.

Any help will be most appreciated!

---

In your first script, COMMIT is part of the stored procedure...

The BEGIN and END in the stored proc do not define the scope (start+finish of the stored proc body): the batch does, which is the next GO (or end of script)

So, changing spacing and adding comments

BEGIN TRANSACTION;
GO 
--start of batch. This comment is part of the stored proc too
ALTER PROCEDURE [dbo].[pvd_sp_job_xxx]
    @jobNum varchar(255)
AS
BEGIN --not needed
    SET NOCOUNT ON;
    UPDATE tbl_ho_job SET delete='Y' WHERE job = @job;
END --not needed
--still in the stored proc
COMMIT TRANSACTION;
GO--end of batch and stored procedure

To check, run

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.pvd_sp_job_xxx'))

---

Although this is a old post, the question is still in my mind after I compiled one of my procedure successfully without any begin transaction,commit transaction or GO. And the procedure can be called and produce the expected result as well. I am working with SQL Server 2012. Does it make some change

I know this is for an answer. But words are too small to notice in comment section.