‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch

Problem

You have a SQL query like this:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'spGetAllPosts') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].spGetAllPosts

CREATE PROCEDURE [dbo].spGetAllPosts
AS
BEGIN
    SELECT * FROM Posts
END

While executing this you get the following error:

‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch

Solution

The error message says it all: the CREATE PROCEDURE statement cannot be executed after other queries in the same batch of queries. The solution is to execute the queries separately. How you do that depends on if you’re using SSMS/sqlcmd/osql or executing from C#.

If you’re executing from SSMS (or sqlcmd/osql)

Add the keyword GO right before CREATE PROCEDURE. This is the default batch separator in SSMS. It splits the query into multiple batches. In other words, it executes CREATE PROCEDURE by itself in its own batch, therefore solving the problem of it needing to be the first statement in a batch.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'spGetAllPosts') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].spGetAllPosts
GO
CREATE PROCEDURE [dbo].spGetAllPosts
AS
BEGIN
    SELECT * FROM Posts
END

If you’re executing from C#

You can’t use the GO keyword in C#. Instead you have to execute the two queries separately. The best way to do that is to execute the first part, then change the CommandText and execute the second part.

using System.Configuration;
using System.Data.SqlClient;

namespace SQLBatchError
{
    class Program
    {
        static void Main(string[] args)
        {
            string dropProcQuery = 
                @"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'spGetAllPosts') AND type in (N'P', N'PC'))
                    DROP PROCEDURE[dbo].spGetAllPosts";
            string createProcQuery = 
                @"CREATE PROCEDURE [dbo].spGetAllPosts
                AS
                BEGIN
                    SELECT * FROM Posts
                END";
            using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLServer"].ConnectionString))
            {
                using (var cmd = new SqlCommand(dropProcQuery, con))
                {
                    con.Open();
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = createProcQuery;
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}

Leave a Comment