SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Dodgy GO Statement


The Dodgy GO Statement

Author
Message
JT Lovell
JT Lovell
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 3

Wow, tough crowd today. Must be a Monday thing since yesterday was a day off for many folks.

I use GO all the time in scripts, especially migration scripts as they have a mix of functions, stored procs, and SQL statements including drop and create statements many of which must be at the start of a batch. Knowing where to put the GOs in the script make all the difference.

One thing about GO that wasn't apparant to me when I first started TSQL was that you couldn't use a local variable that was declared "across a GO". For instance this script gives you an error:

--

DECLARE @myvar varchar(100)
GO

SET @myvar = 'Bob was here!'
SELECT @myvar

--

In such a short script it's easy to fix, but in larger scripts that need to process in a specific order, this limitation may require using temporary tables or other objects to hold values from one batch to the next.


Mike C
Mike C
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3645 Visits: 1168
Yes, you mentioned that it's not a real Transact-SQL statement. I was expanding on what you said it "isn't" by explaining what it actually "is".

As JT noted above, a discussion of how the QA "GO" batch terminator affects scope would be a nice addition to this article.

Another nice addition would be the effect that "GO" has on scripts passed to SQL Server via other methods (such as .NET's SqlCommand).

And to answer the author's question (just in case it wasn't fully evident in the other posts):

Q: "...I don't understand why they allowed any executable statements after the end of stored procedure?"

A: "Because the statements after QA's 'GO' are a different batch."


Personally I think the author picked a good subject, but there are a lot of aspects he skipped over that would have made the article a lot more informative.
Simon Sabin
Simon Sabin
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1036 Visits: 107

A couple of points, just to confirm that GO isn't TSQL but a terminator used by DMO and SMO to parse batches of SQL. Whats more with SQLCMD you can put a number after the GO for the preceeding TSQL batch to be repeated.

The biggest issue I have seen is the deployment of sprocs. The easiest way is to concatenate all the files for the store procs together and run that combined file. However if one file doesn't have a go at the end and all the files start with a if exists drop statement you can end up with.

If exists (select 1 ....)

drop proc myfirstProc

go

create proc myfirstProc

begin

-- do some stuff

end

If exists (select 1 ....)

drop proc mysecondProc

go

create proc mysecondProc

begin

-- Do some stuff

end

go

If exists (select 1 ....)

drop proc mythirdProc

go

create proc mythirdProc

begin

-- Do some stuff

end

What you find is that this is likely to run in, however the first time someone runs myFirstProc it will drop mysecondProc Not good.

On final note for those running scripts on SQL 2005 its a must that you start the script with :on error exit. This will avoid the problem detailed above, where one statement fails and but all the others are still run




Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons
Subramanyam Krishnamurthy
Subramanyam Krishnamurthy
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 9

I work in IST time zone. Now I am back to work.

The intention in the article is clear, to highlight the unknown error/issues and its outcome with absence/usage of GO statement.

Thanks guys for your valuable posts.

Cheers,

Krishnamurthy




Mani
Sean Nolan-296153
Sean Nolan-296153
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 117

All you have to do is check out Books Online

GO

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

Syntax

GO

Remarks

GO is not a Transact-SQL statement; it is a command recognized by the osql and isql utilities and SQL Query Analyzer.

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to 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. SQL Query Analyzer and the osql and isql command prompt utilities implement GO differently.


cneuhold
cneuhold
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 13
hi, the behavior you experience is absolutely by design and no mystery. if you take a close look at BOL, you'll recognize that stored procedures NEVER required BEGIN and END statements. this is just a strange habit that some people seem to have. a stored procedure always starts with CREATE PROC and ends with GO (and that's the reason why it has to be the only statement in a batch).



Manoj Mathew
Manoj Mathew
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1

Of Course you do -- If you are going to script in multiple SPs in the same .SQL file. Also if you are going to script in a SP and a grant it some default permissions in the same .SQL file. Also if are going to create an SP and say 'print' out some status messages in the same .SQL file.

We have faced issues issues like this in our system (using VB and SQL2000, with all business logic implemented as SPs). The author is not saying the GO executes anything in the SP, but how a misplaced GO introduces unintentional behaviour into your SP.

M.


Peter Kryszak
Peter Kryszak
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1452 Visits: 3
cneuhold,

Thanks for clarifying. It really seems to me that the T-SQL language is not mature and can't match other compiled languages or PL/SQL.



reddy-296904
reddy-296904
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 16
I don't know why people post such a bad article, It is waste of time and confuse the new people learning the technology. I request the authour to withdraw this article immediately.
karl.brazier
karl.brazier
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 43
Suppose I want to eg. create a view part way through my stored proc. SQL Server will tell me "'CREATE VIEW' must be the first statement in a query batch." In a plain SQL I'd just throw in a GO to terminate the batch before the CREATE VIEW and start a new one.

Seems to me that's a reason for wanting to be able to use GO in a stored proc. Anyone know what to do about this?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search