drop and create SQL Server procedure

Question

I'm trying to drop and create a procedure in a single script. I tried this:

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Foo')
  DROP PROCEDURE Foo
  GO

CREATE PROCEDURE dbo.Foo
-- procedure body here

But I get an error:

Incorrect syntax near 'GO'.

If I remove GO, I get an error:

'CREATE/ALTER PROCEDURE' must be the first statement in a query batch

Update

These scripts are being executed by a Java build tool (Maven)

Answer 1

GO is not actually a valid term in T-SQL, it's merely the separator that the Microsoft management tools use to delimit query batches.

What are you using to run the script? If you're trying to do it in code then you'll need to split it into two statements, perhaps using a regex to split on ^GO$

Answer 2

Try

IF OBJECT_ID ('idhere') IS NOT NULL
   DROP PROCEDURE idhere
GO
CREATE PROCEDURE idhere
@paramsHere PARAMTYPE
AS
BEGIN
     //...code here...
END
GO

This is how I do it, I'm not sure what version of SQL SERVER my work uses, I believe its 2005.

Answer 3

The easiest way I've found for executing a large scripts outside SSMS from a tool is to use the SQLCMD. (iSQL pre sql 2005) This will work with any environment that allows you to run a shell command.

From the MSDN article

The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt, in Query Editor in SQLCMD mode, in a Windows script file or in an operating system (Cmd.exe) job step of a SQL Server Agent job. This utility uses OLE DB to execute Transact-SQL batches.