-----由于料号太多(超15W),发现直接更新会有部分料号不执行,故采用游标方式处理
CREATE PROCEDURE [dbo].[P_Quick_UpdateABC]
AS
DECLARE @cInvCCode NVARCHAR(500);
---定义游标
DECLARE InvCCode CURSOR FOR
SELECT DISTINCT
(cInvCCode)
FROM Inventory
--WHERE cInvCCode<>'DB2010'
--打开游标
OPEN InvCCode;
FETCH NEXT FROM InvCCode
INTO @cInvCCode;
WHILE @@FETCH_STATUS <> -1
BEGIN
UPDATE dbo.Inventory
SET cInvABC = (CASE
WHEN iInvNCost > 500 THEN
'A'
WHEN 100 <= iInvNCost
AND iInvNCost < 500 THEN
'B'
WHEN iInvNCost < 100 THEN
'C'
END
)
WHERE iInvNCost IS NOT NULL
AND cInvCCode = @cInvCCode;
FETCH NEXT FROM InvCCode
INTO @cInvCCode;
END;
--关闭游标
CLOSE InvCCode;
--释放游标
DEALLOCATE InvCCode;