40

I am running a SQL script but getting an error:

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

Here's my code:

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

How can I solve it?

Pang
  • 9,564
  • 146
  • 81
  • 122
user603007
  • 11,416
  • 39
  • 104
  • 168

7 Answers7

56

Run your statement in the following form:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'myproc') AND type in (N'P', N'PC'))
  DROP PROCEDURE [dbo].[myproc]
GO
create PROCEDURE [dbo].[myproc]
AS
BEGIN
    select * from mytable
END
GO

Note the GO batch separator after DROP PROCEDURE

Oleg Dok
  • 21,109
  • 4
  • 45
  • 54
  • 12
    Note also that if GO is followed by a semicolon, the error message will persist. (SQLSrv apparently interprets the semicolon as an empty statement prior to CREATE). This one stumped me for a while. – kmote Jun 01 '12 at 17:56
  • @xr280xr You are right, the VS is divide the script to batches according to GO stmt, but semicolon is not a part of GO – Oleg Dok Mar 21 '13 at 06:42
  • Can we add to this that the 'Go' statement must be on a line by itself? – Nate Feb 06 '17 at 16:23
  • The GO keyword is the secret sauce. Run your script above without GO and you will encounter the same error. – Micah Epps Jan 30 '18 at 14:34
32

The error message you are getting is correct. You can terminate the batch (and start another) with the GO keyword.

Place GO right before your Create procedure statement. The GO statement must be on a line by itself.

jlnorsworthy
  • 3,914
  • 28
  • 34
  • 2
    'The GO statement must be on a line by itself.' Very important part to get this to work. – Nate Feb 06 '17 at 16:22
13

Often I wish to do the reverse of what you are asking. For example if the user has customized a procedure, and I don't want to lose their changes, yet I want to apply a uniform update script for all my clients, I'd like to be able to do something like the following: 

if not exists ( select * from sys.objects 
            where name='myProc' and objectproperty(object_id,'IsProcedure')=1 )
create proc myProc 
as begin
  -- proc stmts here
end
go

This logic would allow me to create something only if it DOESN'T exist, but to my great frustration, SQL Server prevents this too.

I get around this problem easily enough as follows:

if not exists ( select * from sys.objects 
            where name='myProc' and objectproperty(object_id,'IsProcedure')=1 )
exec('create proc myProc 
as begin
  -- proc stmts here
  declare @object int = 0
end')
go

By passing the create proc command as a string and placing it in an exec statement we circumvent the stupid rule that prevents one from doing this in the first place.

Steve L
  • 1,523
  • 3
  • 17
  • 24
9

My problem went away after I added the following statements:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
Cody Gray - on strike
  • 239,200
  • 50
  • 490
  • 574
Pradono
  • 99
  • 1
  • 1
4

You might want to try this to create stored procedure in another database rather than create it in current context.

set @myScript = 'exec '+ QUOTENAME(@DBName) + '..sp_executesql N''create PROCEDURE [dbo].[myproc]
AS
BEGIN
    select * from mytable
END'''
execute(@myScript)
Will Wu
  • 553
  • 4
  • 15
0
DECLARE @rn INT = 1, @dbname varchar(MAX) = '';

DECLARE @myScript varchar(MAX) = '';
WHILE @DBName IS NOT NULL 
BEGIN


 SET @DBName = (SELECT name FROM (SELECT name, ROW_NUMBER() OVER (ORDER BY name) rn 
        FROM sys.databases WHERE name  IN ('RBAC','sakila')) t WHERE rn = @rn);
    
set @myScript = 'exec '+ QUOTENAME(@DBName) + '..sp_executesql N''Alter PROCEDURE [dbo].[myproc]
AS
BEGIN
    select  765
END'''
execute(@myScript)
SET @rn = @rn + 1;
END;
Suraj Rao
  • 29,388
  • 11
  • 94
  • 103
user3471974
  • 1
  • 1
0

Try putting the 'GO' key at the beginning of the procedure query.

Oğuz
  • 1