SQL Server script: ALTER PROCEDURE - Executing multiple ALTER PROCEDURE into one script without having to select each of the ALTER one after another

Question

I know this is not a big issue, but it tickles me anyway.

I have a SQL Server 2005 script to create new data tables, constraints, altering some tables to add columns, altering procedures to take the table changes into account, etc.
Everything runs fine until the script encounters my ALTER PROCEDURE statements.
The error message is as follows:

"Msg 156, Level 15, State 1, Procedure cpromo_Get_ConsultDetails_PromotionBan, Line 59 Incorrect syntax near the keyword 'PROCEDURE'.

Here's a sample of my script:

ALTER PROCEDURE [dbo].[cpromo_Get_ConsultDetails_PromotionBan] 
(
 @idPromoBan int, 
 @uid int 
)
AS
begin
 set nocount on;

 /* 1-  detail de la promo */
 SELECT p.[nopromo], p.[StartDate], p.[EndDate], p.[DateText]
 FROM [cpromo_PromotionBanniere] as pb
 INNER JOIN [cpromo_Promotions] as p ON p.[idPromo] = pb.[idPromo]
 WHERE (pb.[idPromoBan] = @idPromoBan)

 /* 2 - cartes de la promo */
 SELECT pis.[idCardText], ct.[nom], ct.[descr], ct.[prix], ct.[prixCoupon], ct.[qtyItem], i.[Impact]
 FROM [cpromo_PromotionsItems] as pis
 INNER JOIN [cpromo_Impacts] as i ON i.[idImpact] = pis.[idImpact] 
 INNER JOIN [cpromo_CardText] as ct ON ct.[idCardText] = pis.[idCardText]
 WHERE (pis.[idPromoBan] = @idPromoBan)
 ORDER BY i.[iorder], ct.[nom];

 /* 3 - pvedettes opti */
 SELECT m.[idCardText], m.[qtyCardL], m.[qtyCardM], m.[qtyCardMG], m.[qtyCardS],
     ISNULL(m.[qtyCardMini], 0) as qtyCardMini,
     ISNULL(m.[qtyCardMiniPTJ], 0) as qtyCardMiniPTJ
 FROM [cpromo_MEMCards] as m
 INNER JOIN [cpromo_CardText] as ct ON ct.[idCardText] = m.[idCardText]
 WHERE (m.[idPromoBan] = @idPromoBan)
 ORDER BY ct.[nom];


 /* 4 - cart */
 SELECT [idCartEl], [idCardText], [qtyL], [qtyM], [qtyMG], [qtyS],
     ISNULL([qtyMini], 0) as qtyMini,
     ISNULL([qtyMiniPTJ], 0) as qtyMiniPTJ
 FROM [cpromo_UserCarts]
 WHERE ([uid] = @uid AND [idPromoBan] = @idPromoBan);
end


ALTER PROCEDURE [dbo].[cpromo_Get_CartItems_ByPromotionBan] 
(
 @uid int,
 @idPromoBan int 
)
AS
begin
 set nocount on;

 SELECT ct.nom, ct.descr, p.DateText, ct.prix, ct.prixCoupon, ct.qtyItem,
           uc.qtyL, uc.qtyM, uc.qtyMG, uc.qtyS,
     isnull(uc.qtyMini, 0) as qtyMini,
     isnull(uc.qtyMiniPTJ, 0) as qtyMiniPTJ, 3 as qteLimite
 FROM cpromo_UserCarts as uc
 INNER JOIN cpromo_CardText as ct ON ct.idCardText = uc.idCardText 
 INNER JOIN cpromo_PromotionBanniere as pb ON pb.idPromoBan = uc.idPromoBan 
 INNER JOIN cpromo_Promotions  as p ON p.idPromo = pb.idPromo
 WHERE (uc.uid = @uid) AND (uc.idPromoBan = @idPromoBan);
end

The error points toward the first 'end' keyword encountered when double-clicked. What I don't get at all is when selecting one ALTER statement after another, it runs just fine and smooth! When I try to run them all by pressing [F5] with no selection, it gives me the error.

I tried to embed the ALTER statements into another BEGIN...END, but no luck, it says that there's a syntax error near the keyword ALTER...

EDIT: Can it be because I comment the modifications performed after the begin statement?

ALTER PROCEDURE [dbo].[cpromo_Get_ConsultDetails_PromotionBan] 
    (
     @idPromoBan int, 
     @uid int 
    )
    AS
    begin
------------------
-- Added column to take table changes into account blah blah blah...
------------------
     set nocount on;

     /* 1-  detail de la promo */
     SELECT p.[nopromo], p.[StartDate], p.[EndDate], p.[DateText]
     FROM [cpromo_PromotionBanniere] as pb
     INNER JOIN [cpromo_Promotions] as p ON p.[idPromo] = pb.[idPromo]
     WHERE (pb.[idPromoBan] = @idPromoBan)

     /* 2 - cartes de la promo */
     SELECT pis.[idCardText], ct.[nom], ct.[descr], ct.[prix], ct.[prixCoupon], ct.[qtyItem], i.[Impact]
     FROM [cpromo_PromotionsItems] as pis
     INNER JOIN [cpromo_Impacts] as i ON i.[idImpact] = pis.[idImpact] 
     INNER JOIN [cpromo_CardText] as ct ON ct.[idCardText] = pis.[idCardText]
     WHERE (pis.[idPromoBan] = @idPromoBan)
     ORDER BY i.[iorder], ct.[nom];

     /* 3 - pvedettes opti */
     SELECT m.[idCardText], m.[qtyCardL], m.[qtyCardM], m.[qtyCardMG], m.[qtyCardS],
         ISNULL(m.[qtyCardMini], 0) as qtyCardMini,
         ISNULL(m.[qtyCardMiniPTJ], 0) as qtyCardMiniPTJ
     FROM [cpromo_MEMCards] as m
     INNER JOIN [cpromo_CardText] as ct ON ct.[idCardText] = m.[idCardText]
     WHERE (m.[idPromoBan] = @idPromoBan)
     ORDER BY ct.[nom];


     /* 4 - cart */
     SELECT [idCartEl], [idCardText], [qtyL], [qtyM], [qtyMG], [qtyS],
         ISNULL([qtyMini], 0) as qtyMini,
         ISNULL([qtyMiniPTJ], 0) as qtyMiniPTJ
     FROM [cpromo_UserCarts]
     WHERE ([uid] = @uid AND [idPromoBan] = @idPromoBan);
    end

Thank's for any help or any cue.

Following the recommendations, I inserted "GO" after every single ALTER PROCEDURE begin...end, then it reveals another syntax error near "ALTER". — Will Marcouiller, Sep 16 '09 at 12:57
I did try removing all of the parameters parenthesis as suggested by another programmer here (who I regret not ta have had the time to memorize his name), but still have the error message Msg 156, Level 15, State 1, Procedure cpromo_Get_ConsultDetails_PromotionBan, Line 59 Incorrect syntax near the keyword 'ALTER'. It is points me at the first "end" keyword when I double-click it. — Will Marcouiller, Sep 16 '09 at 13:14
Shall I consider to say that what I did is copied-and-pasted the generated ALTER PROCEDURE script for each of the procedures one below another? — Will Marcouiller, Sep 16 '09 at 13:16
I know it's long and not very funny to read. I'm sorry. This project is at a client site where I don't have much support from the internal programming team. — Will Marcouiller, Sep 16 '09 at 14:08
Well, I succeeded to solve my syntax errors. I wish to thank all of you for the answer I will set as the right solutions. It is all your answer. — Will Marcouiller, Sep 16 '09 at 15:13

Will Marcouiller · Accepted Answer · 2009-09-16T15:31:14.707

This answer is not mine as it is the result of all the answers I have gotten. Each answer has a part of the solution, so I wanted to put an answer with all the points to the solution.

Insert a "GO" statement between each and every ALTER PROCEDURE statement (Everyone who answered)
Make sure there are no invisible characters in the white space area (Arvo)
The BEGIN...END statements revealed to be unnecessary (Mayo)
The semi-colons removal within the AS...GO as per procedure core seemed to cause some trouble either (Mayo)
The comments within the procedure core as described in my question's edit don't matter, it didn't cause any error once the above points were checked (Myself)

Hope this will help someone someday.

Thanks to everyone, credits go to all of you!

score 5 · Answer 2 · answered Sep 16 '09 at 12:53

5

insert "go" between alter statemnts

answered Sep 16 '09 at 12:53

Matt Wrock

6,590
29
23

Thank Matts Wrock for your answer. I tried it as commented in my question, but then I got a syntax error near the keyword ALTER although when ran as a single ALTER statement (when selected) all of them runs just fine and smooth. Even after having inserted the "GO" keyword. – Will Marcouiller Sep 16 '09 at 13:06

score 2 · Answer 3 · answered Sep 16 '09 at 12:54

2

Put a GO after your first ALTER PROCEDURE BEGIN ... END

answered Sep 16 '09 at 12:54

bleeeah

3,534
19
25

Thanks for your prompt answer. Did you notice my edit? Can it be something around it? – Will Marcouiller Sep 16 '09 at 13:29
You have no BEGIN ... END in those statments? – bleeeah Sep 16 '09 at 14:34
I finally used not to include any BEGIN...END statements within the ALTER PROCEDURE statements themselves. – Will Marcouiller Sep 17 '09 at 01:23

score 2 · Answer 4 · answered Sep 16 '09 at 12:55

2

Add a go statement after every end procedure statement. go "Signals the end of a batch of Transact-SQL statements to the SQL Server utilities." (http://msdn.microsoft.com/en-us/library/ms188037.aspx).

answered Sep 16 '09 at 12:55

Ian Kemp

28,293
19
112
138

It now gives me the error: Msg 156, Level 15, State 1, Procedure cpromo_Get_ConsultDetails_PromotionBan, Line 59 Incorrect syntax near the keyword 'ALTER'. – Will Marcouiller Sep 16 '09 at 13:01

score 2 · Answer 5 · answered Sep 16 '09 at 13:14

2

I agree with the go statements - but maybe the parentheses around your parameters are causing the syntax errors? Here's what my team uses...

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

ALTER PROCEDURE dbo.proc
@param1 int
AS
   select 1
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

-- repeat for additional procedures

answered Sep 16 '09 at 13:14

Mayo

10,544
6
45
90

So there wouldn't neither be any begin...end statements? – Will Marcouiller Sep 16 '09 at 13:17
I shall try it anyway. Thanks! – Will Marcouiller Sep 16 '09 at 13:18
Here the error message I get when removing the begin...end statements: A fatal scripting error occurred. Incorrect syntax was encountered while parsing GO. – Will Marcouiller Sep 16 '09 at 13:20
The only other thing that I don't normally see are the semi-colons at the end of some of your statements. Perhaps those prevent the code between the AS and the GO from being interpreted as a single block? – Mayo Sep 16 '09 at 13:25
I did remove the semi-colons and still the error persists. I'm going to try to remove any invisible characters as suggested by Arvo. We never know! Thanks for your time and consideration answering my question. – Will Marcouiller Sep 16 '09 at 13:37

score 2 · Answer 6 · answered Sep 16 '09 at 13:30

2

You may have some invisible characters (nbspace for example) in white space area between first and second procedure. Remove everything between end and subsequent alter (including newlines - resulting in endALTER), then put some line breaks back and write GO on some line.

I've seen that personally, after copying some sample code from net :)

answered Sep 16 '09 at 13:30

Arvo

10,349
1
31
34

Thanks! That's definitely something I didn't think of! :-) And God knows it happens! Hehehe... I'll try that! – Will Marcouiller Sep 16 '09 at 13:32
This solved a part of my problem as the number of errors reported decreased dramatically, which is not dramatic at all! ;-) Thanks! – Will Marcouiller Sep 16 '09 at 13:55

SQL Server script: ALTER PROCEDURE - Executing multiple ALTER PROCEDURE into one script without having to select each of the ALTER one after another

6 Answers6

Linked