我有以下存储进程不起作用,每次运行它时它都会停留在无限循环中。SP 的目标是遍历每行的产品表以评估结果,如果计数行时的结果大于 0,则必须将代码插入表中,如果结果为 0,则必须根据第三个查询插入记录。
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @QUERY = count (*) from (
SELECT TOP 1 [ID],[code],[FEC_MAX],[FUENTE]FROM PRODUCTO
WHERE ( FUENTE = 1 OR FUENTE =2 OR FUENTE = 3 ) and code = @CODE
order by FEC_MAX DESC
) a
IF @QUERY > 0
BEGIN
INSERT INTO TEST
SELECT @code,@fecha,@NOMBRE, @FUENTE , @ID ;
END
if @QUERY < 0
INSERT INTO TEST
SELECT top 1 @code,@fecha,@NOMBRE, @FUENTE , @ID
FROM TEST
WHERE code = @code
order by FUENTE ASC
END
噗嗤
我认为您可能缺少从循环中获取下一个。
从@@FETCHSTATUS返回值
- 0 获取语句成功
- -1 获取语句失败或行超出结果集
- -2 获取的行丢失。
- -9 游标未执行读取操作。
光标停留在第一行,@@FETCHSTATUS将始终返回成功。在循环中添加 FETCH NEXT FROM ,如下所示,假设您已正确声明光标。它应该看起来像这样:
DECLARE YourCursorName CURSOR FOR
SELECT col
FROM PRODUCTO
OPEN YourCursorName;
FETCH NEXT FROM YourCursorName;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @QUERY = count (*) from (
SELECT TOP 1 [ID],[code],[FEC_MAX],[FUENTE]FROM PRODUCTO
WHERE ( FUENTE = 1 OR FUENTE =2 OR FUENTE = 3 ) and code = @CODE
order by FEC_MAX DESC
) a
IF @QUERY > 0
BEGIN
INSERT INTO TEST
SELECT @code,@fecha,@NOMBRE, @FUENTE , @ID ;
END
if @QUERY < 0
INSERT INTO TEST
SELECT top 1 @code,@fecha,@NOMBRE, @FUENTE , @ID
FROM TEST
WHERE code = @code
order by FUENTE ASC
FETCH NEXT FROM YourCursorName
END
CLOSE YourCursorName;
DEALLOCATE YourCursorName;