选择返回行的光标，右数0行手动运行

Question

我有这个游标声明：

 DECLARE CursorArticulo CURSOR FOR 
     SELECT HstAt.IdArticulo, SUM(HstAt.Cantidad) As SumaCantidad, 
      HstAt.Precio 
     FROM HstArticulosTickets hstAT INNER JOIN HstTickets HstT 
      ON hstAT.IdTicket=hstT.IdTicket 
     WHERE [email protected] 
        AND [email protected] 
       AND DATEDIFF(day,@par_Fecha,HstT.FechaHoraTicket)=0 
    GROUP BY IdArticulo, Precio 
    ORDER BY IdArticulo 

@UsuarioAct和@ZonaAct从另一个光标获得的参数。 @par_Fecha参数是存储过程的输入参数。

如果我运行存储过程，在这个游标中，我从来没有得到一个单一的行。永远不要进入典型的WHILE @@ FETCH_STATUS = 0循环。

我尝试在查询分析器复制选择代码并用值替换参数，我得到正确的行。

我在SQL Server 2008中运行这个。

为什么会发生这种情况？

谢谢大家。 。

编辑：

全部存储的过程代码：

ALTER PROCEDURE [DBO] [paCreTablaHojaDeCajaMA] @par_Fecha AS DATETIME AS BEGIN - SET NOCOUNT ON加入防止额外的结果集 - 干扰SELECT语句。 SET NOCOUNT ON;

DELETE FROM dbo.TmpDetalleHojaDeCajaDiaria 
DELETE FROM dbo.TmpMaestraHojaDeCajaDiaria 

INSERT INTO TmpMaestraHojaDeCajaDiaria 
    (NumUsuario, ZonaAsignada, TipoUsuario, NumPDA, ImporteUsuarioZona) 
SELECT 
    hstZA.NumUsuario, hstZA.NumZonaAsignada, 
     (SELECT TipoUsuario FROM Usuarios U WHERE U.NumUsuario=hstZA.NumUsuario) AS TipoUsuario, 
     (SELECT NumPDA FROM Usuarios U WHERE U.NumUsuario=hstZA.NumUsuario) AS NumPDA, 
     (SELECT SUM(hstT.ImporteTotal) FROM HstTickets hstT 
      WHERE hstT.NumUsuarioEmisor=hstZA.NumUsuario 
      AND hstT.NumZona=hstZA.NumZonaAsignada 
      AND DATEDIFF(day,hstZA.Fecha,HstT.FechaHoraTicket)=0) AS ImporteUsuarioZona 
FROM hstZonasAsignadas hstZA 
WHERE DATEDIFF(day,hstZA.Fecha,@par_Fecha)=0 
ORDER BY NumUsuario 


DECLARE @UsuarioAct NCHAR(4) 
DECLARE @ZonaAct SMALLINT 
DECLARE @IdUnicoAct INTEGER 
DECLARE @IdArticulo INTEGER 
DECLARE @NombreArticulo NCHAR(50) 
DECLARE @PrecioUd MONEY 
DECLARE @SumaCantidad INTEGER 

DECLARE CursorMaestra CURSOR FOR 
    SELECT NumUsuario, ZonaAsignada, IdUnico FROM TmpMaestraHojaDeCajaDiaria 
    ORDER BY NumUsuario 

OPEN CursorMaestra 

PRINT 'CURSOR ABIERTO' 

-- Vamos a por el primero 
FETCH NEXT FROM CursorMaestra INTO @ZonaAct, @UsuarioAct, @IdUnicoAct 

WHILE @@FETCH_STATUS = 0 
BEGIN 

    PRINT @ZonaAct 
    PRINT @UsuarioAct 

    DECLARE CursorArticulo CURSOR FOR 
     (SELECT HstAt.IdArticulo, SUM(HstAt.Cantidad) As SumaCantidad, HstAt.Precio 
     FROM HstArticulosTickets hstAT INNER JOIN HstTickets HstT 
      ON hstAT.IdTicket=hstT.IdTicket 
     WHERE [email protected] 
           AND [email protected] 
           AND DATEDIFF(day,@par_Fecha,HstT.FechaHoraTicket)=0 
     GROUP BY IdArticulo, Precio) 

    OPEN CursorArticulo 
    PRINT ' CURSOR ABIERTO' 

    -- Vamos a por el primero 
    FETCH NEXT FROM CursorArticulo INTO @IdArticulo, @SumaCantidad, @PrecioUd 

    PRINT @@FETCH_STATUS 
    WHILE @@FETCH_STATUS = 0 
    BEGIN 

     SELECT @NombreArticulo = NombreArticulo FROM Articulos 
      WHERE IdArticulo = @IdArticulo 

     PRINT @NombreArticulo 

     INSERT INTO TmpDetalleHojaDeCajaDiaria 
      (NumUsuario, ZonaAsignada, IdArticulo, NombreArticulo, PrecioUD, CantidadZonaUsuario, IdUnicoMaestra) 
     VALUES 
      (@UsuarioAct, @ZonaAct, @IdArticulo, @NombreArticulo, @PrecioUd, @SumaCantidad, @IdUnicoAct) 

     FETCH NEXT FROM CursorArticulo INTO @IdArticulo, @SumaCantidad, @PrecioUd 



    END 

    CLOSE CursorArticulo 
    DEALLOCATE CursorArticulo 

    PRINT ' CURSOR CERRADO' 

    FETCH NEXT FROM CursorMaestra INTO @ZonaAct, @UsuarioAct, @IdUnicoAct 
END 

CLOSE CursorMaestra 
DEALLOCATE CursorMaestra 

PRINT 'CURSOR CERRADO' 

END

Answer 1

我找到了！

如果你读外游标声明：

DECLARE CursorMaestra CURSOR FOR SELECT NumUsuario，ZonaAsignada，IdUnico FROM TmpMaestraHojaDeCajaDiaria ORDER BY NumUsuario

的顺序是NumUsuario，ZonaAsignada，IdUnico

但后来，在第一个FETCH中：

FETCH NEXT FROM CursorMaestra INTO @ZonaAct，@UsuarioAct，@IdUnicoAct

我改变了顺序，正确的是@UsuarioAct，@ZonaAct，@IdUnicoAct。

因此，User（UsuarioAct）和Zone（ZonaAct）数据是错误的。

谢谢大家的提示。

Answer 2

你可以尝试添加括号的选择像

DECLARE CursorArticulo CURSOR FOR （ SELECT HstAt.IdArticulo，SUM（HstAt.Cantidad）作为SumaCantidad，HstAt.Precio FROM HstArticulosTickets hstAT INNER JOIN HstTickets HstT ON hstAT.IdTicket = hstT.IdTetet WHERE [email protected] AND [email protected] 和DATEDIFF（日，@ par_Fecha，HstT.FechaHoraTicket）= 0 GROUP BY IdArticulo，PRECIO ORDER BY IdArticulo ）

是变量@UsuarioAct和@ZonaAct得到填充？你可以尝试用手动查询尝试替换那个吗？

Answer 3

是否已将变量@UsuarioAct和@ZonaAct声明为正确的数据类型？

也许隐式转换会微妙地改变数据的含义？

Answer 4

由于您最初在内部游标上获得@@ FETCH_STATUS = -1，因此我会断定您的SELECT语句不返回任何行。你提到你在执行查询时得到结果，但请尝试以下方法：第一BEGIN ... END块的内容替换，只有下面的代码，看看你得到的结果集：

SELECT  HstAt.IdArticulo, SUM(HstAt.Cantidad) As SumaCantidad, HstAt.Precio 
FROM  HstArticulosTickets hstAT 
INNER JOIN HstTickets HstT 
     ON hstAT.IdTicket=hstT.IdTicket 
WHERE  [email protected] 
     AND [email protected] 
     AND DATEDIFF(day,@par_Fecha,HstT.FechaHoraTicket)=0 
GROUP BY IdArticulo, Precio 

，并检查是否得到任何结果。如果你不这样做，那么删除WHERE子句中的一些过滤器......