SQLServerCentral / Discuss Content Posted by Subramanyam Krishnamurthy / Article Discussions / Article Discussions by Author / The Dodgy GO Statement / Latest Posts

RE: The Dodgy GO Statement

karl.brazier — Fri, 04 Sep 2009 09:00:05 GMT

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?

RE: The Dodgy GO Statement

reddy-296904 — Thu, 23 Feb 2006 13:42:00 GMT

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.

RE: The Dodgy GO Statement

Peter Kryszak — Wed, 22 Feb 2006 08:55:00 GMT

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.

RE: The Dodgy GO Statement

Manoj Mathew — Wed, 22 Feb 2006 03:44:00 GMT

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.

RE: The Dodgy GO Statement

cneuhold — Wed, 22 Feb 2006 01:35:00 GMT

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).

RE: The Dodgy GO Statement

Sean Nolan-296153 — Tue, 21 Feb 2006 22:10:00 GMT

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

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.

RE: The Dodgy GO Statement

Subramanyam Krishnamurthy — Tue, 21 Feb 2006 21:20:00 GMT

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

RE: The Dodgy GO Statement

Simon Sabin — Tue, 21 Feb 2006 15:20:00 GMT

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

create proc myfirstProc

begin

-- do some stuff

end

If exists (select 1 ....)

drop proc mysecondProc

create proc mysecondProc

begin

-- Do some stuff

end

If exists (select 1 ....)

drop proc mythirdProc

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

RE: The Dodgy GO Statement

Mike C — Tue, 21 Feb 2006 09:36:00 GMT

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.

RE: The Dodgy GO Statement

JT Lovell — Tue, 21 Feb 2006 09:21:00 GMT

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.

RE: The Dodgy GO Statement

sheepoo — Tue, 21 Feb 2006 09:06:00 GMT

Colin, thanks for the backup.That was exactly my point: You can live without GO as far as creating Stored Procedures are concerned (this being the point discussed in the article)

RE: The Dodgy GO Statement

colin naylor — Tue, 21 Feb 2006 09:04:00 GMT

No, you're not missing anything. You don't have to use the GO word when creating stored procs.

RE: The Dodgy GO Statement

sheepoo — Tue, 21 Feb 2006 08:59:00 GMT

I am having real trouble understanding where I would use this 'approach' in real-world scenarios. In my experience with SQL Server (almost 2 years now) I have not come across one situation where the GO keyword caused me trouble. I use the SQLIDE Pro from Imceda/QUEST and the only time it uses the GO keyword is to doSET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOat the start and GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ON GOat the end: I never had to use the GO keyword within a stored procedure. Maybe I am missing something here

RE: The Dodgy GO Statement

colin naylor — Tue, 21 Feb 2006 07:37:00 GMT

I think I already said that GO wasn't a real sql statement in my earlier message.

Are we surprised that we haven't heard from the author?

RE: The Dodgy GO Statement

Mike C — Tue, 21 Feb 2006 07:24:00 GMT

"GO" is not a real SQL statement. It's a batch termination indicator for Query Analyzer. You might want to point out that trying to use "GO" in your scripts outside of Query Analyzer (like in a .NET SqlCommand), you can expect to catch some exceptions.

RE: The Dodgy GO Statement

Peter Kryszak — Tue, 21 Feb 2006 06:41:00 GMT

I wonder if some of the confusion is from people transitioning from Oracle PL/SQL and other compiled languages where the language is much more sophisticated and clear. CREATE/BEGIN/END have clear syntactical and semantic roles in PL/SQL. In fact, the END statement includes the name of the procedure or function that is being defined.SUB/END SUB and FUNCTION/END FUNCTION in VB.NET are also very clear.T-SQL still seems like a toy language compared to others for this and some other reasons.--Peter

RE: The Dodgy GO Statement

Fattman — Tue, 21 Feb 2006 06:30:00 GMT

With you there. There's more to GO than meets the eye, and it doesn't even have to be "GO" either!

Thort it might be fun to share this early tale of GO horror (I was a lot younger then):

SQL 7, asked to implement a script from a (VB) developer to add a column to a table on a remote, client mission-critical, really, really, really important customer address db.

So, did I double-check the code? No.

Did I test it on a local db? No.

Can you see a disaster coming? Oh, yes...

Run the script. See the script run. See the error.

Turns out VB Fly Boy had scripted the change from Enterprise Mangler and it went something like this:

SELECT * INTO newtable FROM oldtable

DROP TABLE oldtable

CREATE TABLE oldtable (with shiny new column)

INSERT oldtable SELECT * FROM newtable

Okey-dokey, except the first SELECT INTO failed. But, thanks to the joys of "GO" the script ploughed on, dropped the table etc. So, ended up with the new table with its new column and no data!

Oops. Well, that'd be ok 'cos the local dba on the ground will have a back up. Well, yes, from 3 months ago.

Lots and lots of lessons learned there. I now spend my days doing macrame.

RE: The Dodgy GO Statement

Alan Harley — Tue, 21 Feb 2006 02:00:00 GMT

Do you really understand what happens when you create a stored procedure?

Interesting question! I agree with the above poster. The BEGIN and END statements are nothing to do with stored procedures but are simply used to logically group statements in IF's/ELSE's, WHILE's and CASES's.

RE: The Dodgy GO Statement

colin naylor — Tue, 21 Feb 2006 00:10:00 GMT

The author seems to think that begin and end statements seem to do something in stored procedures where they do not.

You can put a begin and end statement around whatever you like, but it wont stop the batch from continuing on to what comes next.

You also don't even need a GO statement in order to create a stored procedure. The authors seems to have totally missed the purpose of the GO statement.

Did you know, that SQL Server doesn't even understand a GO statement. It is not transact SQL!

Hope this helps.

The Dodgy GO Statement

Subramanyam Krishnamurthy-120784 — Wed, 01 Feb 2006 09:16:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sKrishnamurthy/thedodgygostatement.asp