为什么我的游标查询返回0值 [英] why my cursor query return 0 value

问题描述

为什么我的存储过程返回0值而不是goods1，goods2 ,?

Why does my stored procedure return 0 value instead of goods1,goods2,?

DECLARE @vendor_name nvarchar(50),@message varchar(80);

    
DECLARE vend_cursor CURSOR
    FOR SELECT DISTINCT dbo._Goods.god_Name
     FROM dbo._Roshani INNER JOIN
                     dbo._RoshaniGoods ON dbo._Roshani.rsh_id = dbo._RoshaniGoods.rsg_rshId INNER JOIN
                   dbo._Goods ON dbo._RoshaniGoods.rsg_godId = dbo._Goods.god_Id
                      where rsh_rgiId=@rgi_Id
OPEN vend_cursor
FETCH NEXT FROM vend_cursor
INTO  @vendor_name;

WHILE @@FETCH_STATUS=0
BEGIN
    --PRINT ' ';
    SELECT @message = ', ' + @vendor_name;
FETCH NEXT FROM vend_cursor
INTO  @vendor_name;
    
end
PRINT @message;
CLOSE vend_cursor;
DEALLOCATE vend_cursor;

推荐答案

在打印操作之后，您需要获取下一个值.由于仅获取一次，因此"@@ FETCH_STATUS"的值保持不变. 0，从而产生无限循环.

after the print operation you need the fetch the next value. Since you are fetching once only, value of "@@FETCH_STATUS" is remaining <> 0 and hence the infinite loop.

首先检查查询结果

Check the result of the query first

SELECT DISTINCT dbo._Goods.god_Name
FROM dbo._Roshani 
  INNER JOIN dbo._RoshaniGoods ON dbo._Roshani.rsh_id = dbo._RoshaniGoods.rsg_rshId 
  INNER JOIN dbo._Goods ON dbo._RoshaniGoods.rsg_godId = dbo._Goods.god_Id
WHERE rsh_rgiId=@rgi_Id

它会返回2个值吗?

Does it return 2 values?

SET @message = @message + ',' + @vendor_name

DECLARE @vendor_name varchar(50),@message varchar(80);
DECLARE vend_cursor CURSOR
FOR SELECT DISTINCT dbo._Goods.god_Name
FROM dbo._Roshani INNER JOIN
dbo._RoshaniGoods ON dbo._Roshani.rsh_id = dbo._RoshaniGoods.rsg_rshId INNER JOIN
dbo._Goods ON dbo._RoshaniGoods.rsg_godId = dbo._Goods.god_Id
where rsh_rgiId=@rgi_Id 
And isnull(dbo._Goods.god_Name,"0") <>  "0" -- You will need this line
OPEN vend_cursor
FETCH NEXT FROM vend_cursor
INTO @vendor_name;
 <code></code>
	WHILE @@FETCH_STATUS=0
	BEGIN
			--PRINT '' '';
			SELECT @vendor_name; ----- Products From Vendor:  @vendor_name;
			 
	FETCH NEXT FROM vend_cursor INTO @vendor_name; -- You Forgot this line
	END
PRINT @message;
CLOSE vend_cursor;
DEALLOCATE vend_cursor;<pre><code><pre>

这篇关于为什么我的游标查询返回0值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！