Keyword GO - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Keyword GO

Author

Topic

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-03 : 15:24:32

Hi all,

the following is just a part of a large .sql file that I execute from Cmd with the utility ISQL in multiple machine (win2000) and SQL 2000.
I run to an error every time I add Go keyword.

use DB
go
set nocount on
go
IF  @@VERSION LIKE '%6.5%' 
BEGIN
print 'Im 6.5 system'
END

ELSE
BEGIN
IF EXISTS (select * from dbo.sysobjects where name='table1')
begin
    DROP table dbo.table1
end
create table table1 (value1 varchar(255) null)
go
END

Error

Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 95
Incorrect syntax near the keyword 'END'.

when I remove the Go keyword it will be parsed successfully.
Any idea please?

--------------------------
Get rich or die trying
--------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-03 : 15:28:11

GO is a batch separator
so if you add it before END it starts a new batch with just an orphaned END statement which is cause of the error.

if you want to add it, it should be like


use DB
go
set nocount on
go
IF  @@VERSION LIKE '%6.5%' 
BEGIN
print 'Im 6.5 system'
END

ELSE
BEGIN
IF EXISTS (select * from dbo.sysobjects where name='table1')
begin
    DROP table dbo.table1
end
create table table1 (value1 varchar(255) null)
END
go

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-03 : 15:52:31

Thanks visakh16 a lot,

how about this sample:

BEGIN
IF EXISTS (select * from dbo.sysobjects where name='import')
begin
    DROP table dbo.import
end
GO
create table import (value1 varchar(255) null)
IF EXISTS (select * from dbo.sysobjects where name='audit')
begin
    DROP table audit
end
END
GO

I have placed the the GO after end and still cause an error!
I guess I'm missing something I didn't understand !!

I just want to know where to put them EXACTLY to optimize the script because these Goes are causing so many errors.

--------------------------
Get rich or die trying
--------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-03 : 15:56:36

here you've just opposite. you've an orphaned BEGIN without end.

it should be


BEGIN
IF EXISTS (select * from dbo.sysobjects where name='import')
begin
    DROP table dbo.import
end
END 
GO
BEGIN 
create table import (value1 varchar(255) null)
IF EXISTS (select * from dbo.sysobjects where name='audit')
begin
    DROP table audit
end
END
GO

and if they all have to be in same block then there's no need of GO in between

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-03 : 16:01:59

If you take everything between two adjacent GO statements (or between the beginning of the script and the first GO statement, or the last GO statement and the end of the script) and paste it on a new query window, it should be able to stand on its own.



BEGIN
IF EXISTS (select * from dbo.sysobjects where name='import')
begin
    DROP table dbo.import
end
GO

create table import (value1 varchar(255) null)
IF EXISTS (select * from dbo.sysobjects where name='audit')
begin
    DROP table audit
end
END
GO

Since in your code one BEGIN/END pair spans across a GO, the parser does not like that. So remove the first begin and the last end, or add additional END and BEGIN to match.



BEGIN
IF EXISTS (select * from dbo.sysobjects where name='import')
begin
    DROP table dbo.import
end
END
GO
BEGIN
create table import (value1 varchar(255) null)
IF EXISTS (select * from dbo.sysobjects where name='audit')
begin
    DROP table audit
end
END
GO

Edit: Or what Visakh said :)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-08-03 : 16:05:06

Just out of curiosity, have you tested this without any GO separators? I did and it runs fine. Don't use GO unless you absolutely need it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-03 : 16:12:51

quote:
Originally posted by robvolk

Just out of curiosity, have you tested this without any GO separators? I did and it runs fine. Don't use GO unless you absolutely need it.

yep that's the point

I think the GO in posted code came from copy paste of CREATE TABLE script from some other code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-03 : 16:19:21

That's clear, thank you all

--------------------------
Get rich or die trying
--------------------------

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-03 : 16:22:23

@robvolk

It is running without GO, however, when I run it from the cmd line. I think it causes no results.
Somehow...the script isn't acting properly when you call a .SQL file without GO.
But Thanks,;)

--------------------------
Get rich or die trying
--------------------------

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-08-03 : 16:46:54

Your script isn't SELECTing any data, it has nothing to do with the GO separator. The only output to be expected is the PRINT statement if the version is 6.5. Otherwise that script will have no output at all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources