Why does the CREATE PROCEDURE statement fail when

问题:

I am trying to DROP a stored procedure if it exists and then CREATE it by doing this way:

IF OBJECT_ID('[dbo].[myStoredProc]') IS not NULL
    DROP PROCEDURE dbo.myStoredProc


CREATE PROCEDURE [dbo].[myStoredProc]
(
    @parameter1 BIT
) AS

IF @parameter1 = 1
    BEGIN
             ....

But it complains that :

"CREATE PROCEDURE must be the only statement in the batch"

Question: How can I fix my script to overcome this?

回答1:

You need to put a go at the end of your first logical batch.

IF OBJECT_ID('[dbo].[myStoredProc]') IS not NULL
    DROP PROCEDURE dbo.myStoredProc
go -- you need to add the batch-terminator 'go'


CREATE PROCEDURE [dbo].[myStoredProc]
(
    @parameter1 BIT
) AS

IF @parameter1 = 1
    BEGIN
             ..

回答2:

Adding GO after your IF statement shows that this is the end of your first query batch.

Read more here:

http://msdn.microsoft.com/en-us/library/ms188037.aspx

IF OBJECT_ID('[dbo].[myStoredProc]') IS not NULL
    DROP PROCEDURE dbo.myStoredProc
GO

This will prevent your error from occurring.