SQL Server Transact-SQL Batches

Hello folks,

You might have heard about the batches earlier. But do you really know it deep enough?

Well, you don’t have to worry further more because i am going to give you some heads up:

• A query is a single SQL DML statement.
• A batch is a collection of one or more T-SQL statements.
• The SQL script file and Query analyzer window can contains multiple batches.
• If there are multiple batches, then the batch separator keyword terminates each batch.
• Therefore, it introduces a keyword called “GO”.

Now, I will let you know what GO (Transact-SQL) is all about.

GO (Transact-SQL):

• GO is the default batch separator keyword for T-SQL.
• It signals the end of a batch of Transact-SQL statements to the SQL Server utilities.
• Actually, 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.
• The 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.
• So, 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.
• GO must be the only keyword in the line.
• Syntax for GO is:

GO [count]

Here, the COUNT is a positive integer. The batch preceding GO will execute the specified number of times.

• See the following example:

PRINT 'Piyush';
GO 7

The result can be seen as:

• GO is a utility command that requires no permissions. It can be executed by any user.
• The batch separator can also be modified in the Query Execution Page from the Tools -> Options;

Well, you can edit the name of the batch separator easily from the above.

• The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command.
• The applications based on the ODBC or OLE DB APIs receives a syntax error if they try to execute a GO command. The SQL Server utilities never send a GO command to the server.
• As soon as we terminate a batch i.e. just after a GO command, the batch kills all local variables, temporary variables, and cursors created by that batch.
• See the following examples carefully-

USE TEST;
GO
DECLARE @text VARCHAR(75)
SELECT @text = 'SQLServerGeeks'
GO

Here, the local variable ‘text’ is not at all valid after this GO ends the batch.

USE TEST;
GO
PRINT @text
GO

Since the ‘text’ has not been declared, it will generate an error.

USE TEST;
GO
SELECT @@VERSION;
sp_who
GO

This will also results an error. Therefore, any execution of a stored procedure after the first statement in a batch must include the EXECUTE keyword.

Well this was all about T-SQL batches.

Hope you have enjoyed reading it 🙂

And also comments on this!!

Regards

Piyush Bajaj

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook