Creating a stored procedure Syntax regarding the use of GO

Question

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 package statement, maybe the last statement should not have GO ?

Answer 1

An error has occurred in SQL Server that does not properly parse the GO statement. I suppose this was introduced when you could do GO X and execute X several times.

Sometimes I had to add a comment section (" - ") to cause the parser to stop, rather than cause a syntax error. This was visible when I had 400,000 lines in the code package.

Example:

PRINTING "This is a test."

GO -

PRINTING "This is not a test."

GO 5 -

Answer 2

If you go to "Save as ...", click on the small down arrow on the "Save" button and select "Save with encoding ...", then you can set the line ending for Windows (CR LF). This seems to have solved the problem for me ...

Answer 3

In the SQL query that you are currently using, the question will work correctly. The unformatted sql that you had before Kev edited the message will not. The reason is that you had GO on the same line as sql. It should be on a separate line.

Answer 4

If you copy text to a text editor using Unix / Mac EOL (for example, Notepad ++ supports this), GO is interpreted as being on the same line as the last TSQL statement (as long as you can see new lines on the screen usually). Problem converting EOL to Windows (CRLF) into a text editor. Very difficult.

Answer 5

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 an

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

Pay attention to Line 7 , the original question is Line 1 .

If I put GO on my line, SQL works fine.

Given that your error message says “Line 1”, it looks like for some reason your sql file does not have the correct CR / LF.

Answer 6

You can, of course, have GO at the end of your party. I see nothing wrong with this code as such. Place a semicolon after @Company.

Answer 7

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

It works fine and creates proc. Perhaps you can try placing your code in a BEGIN ... END block and see if the problem persists.

Rajah

Answer 8

you said

Now this is the last statement package, maybe the last statement should not have GO?

This means that these lines are part of a single batch sent to SQL. The fact is that the CREATE PROCEDURE statement (or CREATE FUNCTION or CREATE VIEW) must be the first statement in the package. So put the line “GO” in front of this CREATE statement and see what happens.

Philip

Answer 9

In my case, I copied part of the code from a web page and it seems that I saved the page with a different encoding, I tried SaveAs from SMS with a different encoding, but it did not work.

To fix my problem, I copy the code to NodePad, then save it in ANSI format and reopen the request

Answer 10

No serious company can claim to add GO after each application. Perhaps after each batch.

GO is not a Transact-SQL statement . The separator is understood by tools such as ISQLW (aka. Query analizer), osql, sqlcmd and SSMS (Management Studio). These tools split the SQL file into batches separated by GO (or something like a “batch separator” to be exact, but usually GO), and then send one batch to the server at a time. The server never sees GO, and if it sees it, it will report error 102, incorrect syntax, as you have already seen.