views:

1411

answers:

6

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

  1. 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.
  2. Everything runs fine until the script encounters my ALTER PROCEDURE statements.
  3. 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.

+1  A: 

insert "go" between alter statemnts

Matt Wrock
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
+1  A: 

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

bleeeah
Thanks for your prompt answer. Did you notice my edit? Can it be something around it?
Will Marcouiller
You have no BEGIN ... END in those statments?
bleeeah
I finally used not to include any BEGIN...END statements within the ALTER PROCEDURE statements themselves.
Will Marcouiller
+1  A: 

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).

Ian Kemp
It now gives me the error: Msg 156, Level 15, State 1, Procedure cpromo_Get_ConsultDetails_PromotionBan, Line 59Incorrect syntax near the keyword 'ALTER'.
Will Marcouiller
+1  A: 

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
Mayo
So there wouldn't neither be any begin...end statements?
Will Marcouiller
I shall try it anyway. Thanks!
Will Marcouiller
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
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
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
+1  A: 

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 :)

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

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.

  1. Insert a "GO" statement between each and every ALTER PROCEDURE statement (Everyone who answered)
  2. Make sure there are no invisible characters in the white space area (Arvo)
  3. The BEGIN...END statements revealed to be unnecessary (Mayo)
  4. The semi-colons removal within the AS...GO as per procedure core seemed to cause some trouble either (Mayo)
  5. 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!

Will Marcouiller