drop and create SQL Server procedure

I'm开发者_Python百科 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)

---

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$

---

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.

---

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.

---

It would be better to use this syntax for the existence check:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[foo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[foo]
GO

As written, if there was a foo sproc in any schema it would try to drop it. Not sure if that will make your problem go away though. If you use SSMS, there is an option to script a stored procedure as DROP and CREATE; that syntax should work.

---

Check Jon Galloway's post: Handling "GO" Separators in SQL Scripts - the easy way

It might have the answer you are seeking.