Create alter procedure должна быть первой инструкцией в пакетном запросе

Problem

When you’re executing a CREATE/ALTER statement to create a procedure/view/function/trigger, you get one of the following errors:

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

‘CREATE VIEW’ must be the first statement in a query batch.

‘CREATE FUNCTION’ must be the first statement in a query batch.

‘CREATE TRIGGER’ must be the first statement in a query batch.

You can’t execute these CREATE/ALTER statements with other statements.

Solution

The solution is to execute the CREATE/ALTER statement separately from other statements. 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 statement. This is the default batch separator in SSMS. It splits the query into multiple batches. In other words, it executes the CREATE statement by itself in its own batch, therefore solving the problem of it needing to be the first statement in a batch. Here’s an example:

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
Code language: SQL (Structured Query Language) (sql)

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.Data.SqlClient;

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(ConnectionString))
{
	using (var cmd = new SqlCommand(dropProcQuery, con))
	{
	        //Execute the first statement
		con.Open();
		cmd.ExecuteNonQuery();
		
		//Then execute the CREATE statement
		cmd.CommandText = createProcQuery;
		cmd.ExecuteNonQuery();
	}
}

Code language: C# (cs)

Я пытаюсь создать sql script, но получаю сообщение об ошибке:

‘CREATE/ALTER PROCEDURE’ должен быть первым оператором в запросе пакетный??

Здесь мой код:

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

create PROCEDURE [dbo].[myproc]

AS
BEGIN
    select * from mytable
END
GO