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
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$
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.
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.