title: SQL Server script: ALTER PROCEDURE - Executing multiple ALTER PROCEDURE into one script without having to select each of the ALTER one after another
body: <p>I know this is not a big issue, but it tickles me anyway.</p>
  
  <ol>
  <li>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.</li>
  <li>Everything runs fine until the script encounters my ALTER PROCEDURE statements.</li>
  <li>The error message is as follows: </li>
  </ol>
  
  <blockquote>
    <p>"Msg 156, Level 15, State 1, Procedure
    cpromo_Get_ConsultDetails_PromotionBan,
    Line 59 Incorrect syntax near the
    keyword 'PROCEDURE'.</p>
  </blockquote>
  
  <p>Here's a sample of my script:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>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...</p>
  
  <blockquote>
    <p><strong>EDIT:</strong> Can it be because I comment the modifications performed after the begin statement?</p>
  </blockquote>
  
  <pre><code>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
  </code></pre>
  
  <p>Thank's for any help or any cue.</p>