Database Version Control SQL Server 2008 Drop SP's and Functions

Question

I'm working on versioning our database and now searching for a way to drop all stored procedures and functions from a C# Console Application. I'd rather not create a stored procedure that drops all stored procedures and functions. I has to be some sql executed from C#.

I tried to drop the stored procedure before creating it, but I get this message:

System.Data.SqlClient.SqlException: 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

Script for one SP for example:

DROP PROCEDURE [dbo].[sp_Economatic_LoadJournalEntryFeedbackByData]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [dbo].[sp_Economatic_LoadJournalEntryFeedbackByData]
    @Data VARCHAR(MAX)
AS
BEGIN
    ...
END

So I guess before creating all SP's and functions I'll need to drop all SP's and functions first with one sql script.

Answer 1

you need to put "GO" between each statement block. Please see: http://msdn.microsoft.com/en-us/library/ms188037.aspx

so your above code would become:

DROP PROCEDURE [dbo].[sp_Economatic_LoadJournalEntryFeedbackByData]

-- this makes it execute the above statement, before continuing.
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[sp_Economatic_LoadJournalEntryFeedbackByData]
@Data VARCHAR(MAX)

AS
  BEGIN
    ...
  END

Answer 2

You can check for existence before dropping:

IF EXISTS (SELECT * FROM [dbo].[sysobjects]
           WHERE ID = object_id(N'[dbo].[YourProcName]') AND 
                 OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE [dbo].[YourProcName]
GO

... then create it...

I would add this for each stored procedure you want to create.