Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1094 > unrolled thread

When to Use GO

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  When to Use GO Gene Wirchenko <genew@ocis.net> - 2012-05-30 14:21 -0700
    Re: When to Use GO Erland Sommarskog <esquel@sommarskog.se> - 2012-05-31 07:29 +0000
      Re: When to Use GO Gene Wirchenko <genew@ocis.net> - 2012-05-31 10:07 -0700
        Re: When to Use GO Erland Sommarskog <esquel@sommarskog.se> - 2012-05-31 22:26 +0200
          Re: When to Use GO Gene Wirchenko <genew@ocis.net> - 2012-06-01 09:41 -0700
            Re: When to Use GO Erland Sommarskog <esquel@sommarskog.se> - 2012-06-01 23:23 +0200
              Re: When to Use GO Gene Wirchenko <genew@ocis.net> - 2012-06-03 19:36 -0700
    Re: When to Use GO "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-05-31 06:45 -0400

#1094 — When to Use GO

Dear SQLers:

     I am somewhat confused about when to use GO.  I would like to
avoid it where possible, that is, I would prefer to make my batches as
big as possible.  Trying to find out when GO can be omitted is a bit
of an adventure.

     I have done Web searching to find an answer, but there is not
enough detail, as in, for example:
          http://msdn.microsoft.com/en-us/library/ms188037.aspx

     I have a script where I very early delete the database and
recreate it.  In one recent problem area, I create a procedure.  It
has a GO in front of it, because stored procedures have to be first in
a batch.  Fine.  If I then follow with a CREATE TABLE in the same
batch, I get an error that seems to indicate that the CREATE TABLE has
been included in the procedure.  If I put a GO just after the
procedure, I do not have this problem.

     What am I missing or misunderstanding?

     (I am now putting each of my CREATE PROCEDURE statements (and the
procedure) in its own batch delimited by GO at both the beginning and
the end.  This is a bit cargo cult, but it works.)

     When exactly is GO required?

Sincerely,

Gene Wirchenko

[toc] | [next] | [standalone]

#1096

Gene Wirchenko (genew@ocis.net) writes:
>      I have a script where I very early delete the database and
> recreate it.  In one recent problem area, I create a procedure.  It
> has a GO in front of it, because stored procedures have to be first in
> a batch.  Fine.  If I then follow with a CREATE TABLE in the same
> batch, 

You can't. That CREATE TABLE is part of the procedure. CREATE PROCEDURE must 
be the first command in the batch, and it is the only command in the batch, 
a property it shares with a couple of more commands.

Again, put your source code under version-control and load the files from
a client-side program. Then you don't need to ask these questions.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [next] | [standalone]

#1098

On Thu, 31 May 2012 07:29:17 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>      I have a script where I very early delete the database and
>> recreate it.  In one recent problem area, I create a procedure.  It
>> has a GO in front of it, because stored procedures have to be first in
>> a batch.  Fine.  If I then follow with a CREATE TABLE in the same
>> batch, 
>
>You can't. That CREATE TABLE is part of the procedure. CREATE PROCEDURE must 
>be the first command in the batch, and it is the only command in the batch, 
>a property it shares with a couple of more commands.

     I have not read anything stating about a CREATE PROCEDURE having
to be the only command in a batch.  But, as my post shows, I have
discovered it myself.  Curse incomplete docs.

>Again, put your source code under version-control and load the files from
>a client-side program. Then you don't need to ask these questions.

     I am experimenting in SSMS.  I do not want the overhead.

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#1099

Gene Wirchenko (genew@ocis.net) writes:
>      I have not read anything stating about a CREATE PROCEDURE having
> to be the only command in a batch.  But, as my post shows, I have
> discovered it myself.  Curse incomplete docs.

The third paragraph in the Remarks section for the topic for CREATE 
PROCEDURE in Books Online reads:

   The CREATE PROCEDURE statement cannot be combined with other Transact-SQL 
   statements in a single batch.

>>Again, put your source code under version-control and load the files from
>>a client-side program. Then you don't need to ask these questions.
> 
>      I am experimenting in SSMS.  I do not want the overhead.
 
So you have all your procedure in a single monolithic script. A week
after deployment a bug discovered. Then another bug. And so on. Do
you edit the big script over and over again? And since it creates tables
that already exists, it's pretty useless by then.

But if you want to redo mistakes others have already have made, please 
go ahead.



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1100

On Thu, 31 May 2012 22:26:13 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>      I have not read anything stating about a CREATE PROCEDURE having
>> to be the only command in a batch.  But, as my post shows, I have
>> discovered it myself.  Curse incomplete docs.
>
>The third paragraph in the Remarks section for the topic for CREATE 
>PROCEDURE in Books Online reads:
>
>   The CREATE PROCEDURE statement cannot be combined with other Transact-SQL 
>   statements in a single batch.

     I have not been able to find BOL on Google.  Is there a link?  I
have been using MSDN.  I see now that I missed that bit, but that was
a different page than what I had been reading prviously.

>>>Again, put your source code under version-control and load the files from
>>>a client-side program. Then you don't need to ask these questions.
>> 
>>      I am experimenting in SSMS.  I do not want the overhead.
             ^^^^^^^^^^^^^
>So you have all your procedure in a single monolithic script. A week
>after deployment a bug discovered. Then another bug. And so on. Do
       ^^^^^^^^^^
     The two words that I have highlighted do not mean the same.

>you edit the big script over and over again? And since it creates tables

     Yes, I am experimenting.

>that already exists, it's pretty useless by then.

     Since it starts by dropping the database, no.

>But if you want to redo mistakes others have already have made, please 
>go ahead.

     Overornamentation is a mistake others have made.  I am avoiding
it.

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#1101

Gene Wirchenko (genew@ocis.net) writes:
>      I have not been able to find BOL on Google.  

http://bit.ly/JFqaCg

>      Yes, I am experimenting.

And I am telling you where it ends.
 
>>that already exists, it's pretty useless by then.
> 
>      Since it starts by dropping the database, no.

Weren't you to send this script somewhere else once you were done?
 


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1102

On Fri, 01 Jun 2012 23:23:51 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>      I have not been able to find BOL on Google.  
>
>http://bit.ly/JFqaCg
>
>>      Yes, I am experimenting.
>
>And I am telling you where it ends.
> 
>>>that already exists, it's pretty useless by then.
>> 
>>      Since it starts by dropping the database, no.
>
>Weren't you to send this script somewhere else once you were done?

     Yes, to check that it works there, too.  It is not production
yet.

     We already got stung with backups with different versions not
working.  (I had not run across anything about it about 2008 and 2008
R2.  The gotcha bit.)

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#1097

Gene Wirchenko wrote:
> Dear SQLers:
>
>     I am somewhat confused about when to use GO.  I would like to
> avoid it where possible, that is, I would prefer to make my batches as
> big as possible.  Trying to find out when GO can be omitted is a bit
> of an adventure.
>
The only way I've seen to eliminate it is dynamic sql. 

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web