Removing and creating a SQL Server procedure

Question

I am trying to delete and create a procedure in one 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 message:

Incorrect syntax near 'GO'.

If I delete GO , I get an error message:

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

Update

These scripts are executed by the Java build tool (Maven)

Answer 1

GO is actually not a valid term in T-SQL; it is just a delimiter that Microsoft management tools use to delimit query packets.

What do you use to run the script? If you are trying to do this in code, you need to split it into two statements, possibly using a regular expression to split it into ^GO$

Answer 2

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

From an MSDN article

The sqlcmd utility allows you to enter Transact-SQL statements, a system of procedures, and script files on the command line in the Query Editor in SQLCMD mode in a Windows script or in the operating system (Cmd.exe) to specify the SQL Server Agent job. This utility uses OLE DB to execute Transact-SQL packages.

Answer 3

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 which version of SQL SERVER my work uses, I believe its 2005.

Answer 4

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

 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 foo sproc was in any circuit, he would try to reset it. Not sure if this will make your problem go away. If you are using SSMS, there is a scripted stored procedure option like DROP and CREATE; this syntax should work.

Answer 5

Mark Jon Galloway's post: Processing "GO" sections in SQL scripts - easy way

It may have the answer you are looking for.