drop and create SQL Server procedure

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)

Answers


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$


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.


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.


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.


Need Your Help

Wicket and Spring Integration

java spring wicket

I have a wicket contact form, and i receive the form object. Now i need to pass this object to Spring Service.

keyboard hiding my textview

iphone uiview keyboard uiscrollview uitextview

i have a simple app, it consist of 2 textview, 1 uiview as a coretext subclass, and then 1 scrollview. the others part is subviews from scrollview. I use this scrollview because i need to scroll the

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.