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