‘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