SET @Order = 1
DECLARE CRNov CURSOR FOR SELECT BRAND_ID,SellThru FROM NovTendencyRanking
WHERE CounterID = @CounterID AND YEARMONTH = @YearMonth ORDER BY SellThru DESC
OPEN CRNov
FETCH NEXT FROM CRNov INTO @Brand,@Sell
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE RANKALL SET Nov_Brand = @Brand,Nov_Sell = @Sell
WHERE RANK_ID = @Order
AND CounterID = @CounterID AND YEARMONTH = @YearMonth
SET @Order = @Order + 1
FETCH NEXT FROM CRNov INTO @Brand,@Sell
END
CLOSE CRNov
DEALLOCATE CRNov
上面这段代码当中,如果CounterID = @CounterID AND YEARMONTH = @YearMonth当中的@CounterID和@YearMonth用的是变量,更新操作就不成功,如果换成常量,比如AND CounterID = '0001' AND YEARMONTH = '0910',更新就没有问题,不知道什么原因,我尝试了,在这个过程当中,把@CounterID和@YearMonth保存到一个测试用的表当中,数据都是正确的,不知道为什么会这样子
DECLARE CRNov CURSOR FOR SELECT BRAND_ID,SellThru FROM NovTendencyRanking
WHERE CounterID = @CounterID AND YEARMONTH = @YearMonth ORDER BY SellThru DESC
OPEN CRNov
FETCH NEXT FROM CRNov INTO @Brand,@Sell
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE RANKALL SET Nov_Brand = @Brand,Nov_Sell = @Sell
WHERE RANK_ID = @Order
AND CounterID = @CounterID AND YEARMONTH = @YearMonth
SET @Order = @Order + 1
FETCH NEXT FROM CRNov INTO @Brand,@Sell
END
CLOSE CRNov
DEALLOCATE CRNov
上面这段代码当中,如果CounterID = @CounterID AND YEARMONTH = @YearMonth当中的@CounterID和@YearMonth用的是变量,更新操作就不成功,如果换成常量,比如AND CounterID = '0001' AND YEARMONTH = '0910',更新就没有问题,不知道什么原因,我尝试了,在这个过程当中,把@CounterID和@YearMonth保存到一个测试用的表当中,数据都是正确的,不知道为什么会这样子
CREATE PROCEDURE TendRank
@SessionID VARCHAR(30)
AS
DECLARE @Brand VARCHAR(10)
DECLARE @Sell FLOAT
DECLARE @Order INTDECLARE @YearMonth VARCHAR(10)
SELECT @YearMonth = TYStart + TYEnd FROM FYear WHERE SessionID = @SessionID
DECLARE @ThisYearMonth VARCHAR(6)
SELECT @ThisYearMonth = YearMonth FROM TendencyYearMonth WHERE SessionID = @SessionID
DECLARE @Month INT
SET @Month = CAST(RIGHT(@ThisYearMonth,2) AS INT)--取得柜台ID
DECLARE @CounterID VARCHAR(50)
SELECT @CounterID = CounterID FROM SingleShop WHERE SessionID = @SessionIDSELECT * FROM RANKALL WHERE COUNTERID = @CounterID AND YEARMONTH = @YearMonth
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO RANKALL(COUNTERID,YearMonth,RANK_ID)
SELECT @CounterID,@YearMonth,RANK_ORDER FROM RANK_ORDER
ENDIF @Month = 11
BEGIN
SELECT * FROM NovTendencyRanking WHERE COUNTERID = @CounterID AND YearMONTH = @YearMonth
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO NovTendencyRanking
EXEC TendencyRank 'NOV',@SessionID
END SET @Order = 1
DECLARE CRNov CURSOR FOR SELECT BRAND_ID,SellThru FROM NovTendencyRanking
WHERE CounterID = @CounterID AND YEARMONTH = @YearMonth ORDER BY SellThru DESC
OPEN CRNov
FETCH NEXT FROM CRNov INTO @Brand,@Sell
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE RANKALL SET Nov_Brand = @Brand,Nov_Sell = @Sell
WHERE RANK_ID = @Order
AND CounterID = @CounterID AND YEARMONTH = @YearMonth
SET @Order = @Order + 1
FETCH NEXT FROM CRNov INTO @Brand,@Sell
END
CLOSE CRNov
DEALLOCATE CRNov
END
--UPDATE RANKALL SET Nov_Brand = @Brand,Nov_Sell = @Sell WHERE RANK_ID = @Order
SELECT * FROM RANKALL AND CounterID = @CounterID AND YEARMONTH = @YearMonth
SELECT * FROM RANKALL AND CounterID = '0001' AND YEARMONTH = '0910' 测试下这个结果?看看有没有返回值?
SET Nov_Brand = @Brand
,Nov_Sell = @Sell
FROM RANKALL R
WHERE RANK_ID = @Order
AND CounterID = @CounterID
AND YEARMONTH = @YearMonth你给这三个变量@Brand,@Sell,@Order赋值,如果上面可以执行.那么你的这个结论是不对的!
上面这段代码当中,如果CounterID = @CounterID AND YEARMONTH = @YearMonth当中的@CounterID和@YearMonth用的是变量,更新操作就不成功,至于你这个sp是哪里错了.你自己单步调试下.一步一步用常量替代变量.把数据逐步筛选.然后找出是哪里问题.