SQL Server脚本：ALTER PROCEDURE-在一个脚本中执行多个ALTER PROCEDURE，而不必一个接一个地选择每个ALTER [英] SQL Server script: ALTER PROCEDURE - Executing multiple ALTER PROCEDURE into one script without having to select each of the ALTER one after another
问题描述
我知道这不是什么大问题，但是无论如何它都会让我很烦。
I know this is not a big issue, but it tickles me anyway.
- 我有一个SQL Server 2005脚本来创建新的数据表，约束，更改某些表以添加列，更改过程以考虑到表更改等。
- 一切正常，直到脚本遇到我的ALTER PROCEDURE语句。
- 错误消息如下：
消息156，级别15，状态1，程序
cpromo_Get_ConsultDetails_PromotionBan，
第59行
关键字'PROCEDURE'附近的语法不正确。
"Msg 156, Level 15, State 1, Procedure cpromo_Get_ConsultDetails_PromotionBan, Line 59 Incorrect syntax near the keyword 'PROCEDURE'.
这是我的脚本示例：
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
错误指向双击时遇到的第一个 end关键字。我一无所获，是在选择一个ALTER语句之后，它运行得非常顺畅！当我尝试通过不带选择地按[F5]来全部运行它们时，出现了错误。
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.
我试图将ALTER语句嵌入另一个BEGIN中...结束，但是没有运气，它表明关键字ALTER附近存在语法错误...
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...
编辑：可能是因为我评论了begin语句之后执行的修改吗？
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
感谢您的帮助或提示。
推荐答案
这个答案不是我的，因为它是我得到的所有答案的结果。每个答案都有解决方案的一部分，所以我想对所有要点给出一个答案。
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.
- 插入一个 GO 每个ALTER PROCEDURE语句之间的语句（每个回答的人）
- 确保空白区域（Arvo）中没有不可见的字符
- 显示BEGIN ... END语句是不必要的（Mayo）
- 按照过程核心在AS ... GO中删除分号似乎也引起一些麻烦（Mayo） ）
- 在我的问题的编辑中描述的过程核心中的注释无关紧要，一旦检查了以上几点（我自己），就不会引起任何错误
- 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!
这篇关于SQL Server脚本：ALTER PROCEDURE-在一个脚本中执行多个ALTER PROCEDURE，而不必一个接一个地选择每个ALTER的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
