CODEWITHSUNDEEP
sqlsql-serversql-server-2005stored-procedures
Mike D
Mike D

Reputation: 220

Creating Stored Procedure Syntax, relating to use of GO

Does anyone know why.

CREATE PROCEDURE My_Procedure
    (@Company varchar(50))  
AS  
    SELECT PRD_DATE 
    FROM WM_PROPERTY_DATES 
    WITH (NOLOCK) 
    WHERE PRD_COMPANY = @Company 
GO

Gives me an error message in SQL management studio:

Msg 102, Level 15, State 1, Procedure My_Procedure, Line 1 Incorrect syntax near 'GO'.

Now, this is the last statement of a batch, maybe the last statement should not have a GO ?

Upvotes: 5

Views: 19373

Answers (10)

Sebastian Castaldi
Sebastian Castaldi

Reputation: 9004

In my case I had copied part of the code from a webpage and it seems that saved the page with different encoding, I tried SaveAs from SMS with different encoding, but didn't work.

To fix my issue I copy the code into NodePad, then save it in ANSI format and re-open the query

Upvotes: 0

NeverStopLearning
NeverStopLearning

Reputation: 998

If you copy-paste text from text editor with Unix/Mac EOLs (e.g. Notepad++ supports this) the GO is interpreted as being on the same line as the last TSQL statement (yet on the screen you can see newlines normally). Converting EOLs to Windows (CRLF) in the text editor fixed the problem. Very tricky though.

Upvotes: 3

Dustin Buschow
Dustin Buschow

Reputation: 46

There was a bug released in SQL Server that parses the GO statement incorrectly. I believe it was introduced when you could do GO X, and execute the batch X multiple times.

Sometimes I've had to add a comments section ("--") to force the parser to terminate rather than produce a syntax error. This has been seen when I've had 400,000 lines in a batch of code.

Example:

PRINT "This is a test."

GO --

PRINT "This is not a test."

GO 5 --

Upvotes: 3

utexaspunk
utexaspunk

Reputation: 390

If you go to "Save As...", click on the little down-arrow on the Save button and select "Save with Encoding..." then you can set your Line endings to Windows (CR LF). That seems to have resolved the issue for me...

Upvotes: 12

Philip Kelley
Philip Kelley

Reputation: 40309

You said

Now, this is the last statement of a batch, maybe the last statement should not have a GO ?

This implies that these lines are all part of the same batch submitted to SQL. The thing is, a CREATE PROCEDURE (or CREATE FUNCTION or CREATE VIEW) statement must be the first statement in the batch. So, put a "GO" line in front of that CREATE statement, and see what happens.

Philip

Upvotes: 0

Paul Rowland
Paul Rowland

Reputation: 8352

Error for this sql

ALTER PROCEDURE My_Procedure
    (@Company varchar(50))  
AS  
    SELECT PRD_DATE 
    FROM WM_PROPERTY_DATES 
    WITH (NOLOCK) 
    WHERE PRD_COMPANY = @Company GO

is

Msg 102, Level 15, State 1, Procedure My_Procedure, Line 7
Incorrect syntax near 'GO'.

note the Line 7, original question has Line 1.

If I put the GO on its own line SQL works fine.

Given that your error message says Line 1, it would appear that for some reason there isnt a correct CR/LF happening in your sql.

Upvotes: 1

Raj
Raj

Reputation: 10843

I tried this SQL on my 2008 server by creating a table WM_PROPERTY_DATES and adding 2 columns, PRD_DATE and PRD_COMPANY.

Work just fine and creates the proc. Maybe you can try putting your code in a BEGIN...END block and see if the issue persists.

Raj

Upvotes: 0

Rune Sundling
Rune Sundling

Reputation: 602

The sql you currently have in the question will work properly. The unformatted sql you had before Kev edited the post won't. The reason is that you had the GO on the same line as the sql. It needs to be on a separate line.

Upvotes: 4

Remus Rusanu
Remus Rusanu

Reputation: 294217

No serious company could possibly pretend to add GO after each statement. Perhaps after each batch.

GO is not a Transact-SQL statement. Is a delimiter understood by tools like ISQLW (aka. Query analizer), osql, sqlcmd and SSMS (Management Studio). These tools split the SQL file into batches, delimited by GO (or whatever is the 'batch delimiter' set, to to be accurate, but is usually GO) and then send to the server one batch at a time. The server never sees the GO, and if it would see it then it would report an error 102, incorrect syntax, as you already seen.

Upvotes: -1

Dave Markle
Dave Markle

Reputation: 97671

You can certainly have GO at the end of your batch. I see nothing wrong with this code per se. Put a semicolon after @Company.

Upvotes: 0

Related Questions