Creating Stored Procedure Syntax, relating to 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 statement of a batch, maybe the last statement should not have a GO ?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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

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

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.

Answer 6

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