Select returns 0 rows in a cursor and right number of rows in manual running

Question

Hello:

I've this cursor declaration:

     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 hstT.NumUsuarioEmisor=@UsuarioAct
                  AND HstT.NumZona=@ZonaAct
              AND DATEDIFF(day,@par_Fecha,HstT.FechaHoraTicket)=0
  GROUP BY IdArticulo, Precio
  ORDER BY IdArticulo

The parameters @UsuarioAct and @ZonaAct are obtained from another Cursor. The @par_Fecha parameter is an input parameter for a Stored procedure.

If I run the stored procedure, in this cursor I never get a single row. Never enters into the typical WHILE @@FETCH_STATUS = 0 loop.

I try in query analyzer copying the select code and replacing parameters with values and I get the correct rows.

I'm running this in SQL Server 2008.

Why does this happen?

Thank you all.

EDIT:

Full Stored Procedure Code:

ALTER PROCEDURE [dbo].[paCreTablaHojaDeCajaMA] @par_Fecha AS DATETIME AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. 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 hstT.NumUsuarioEmisor=@UsuarioAct
         AND HstT.NumZona=@ZonaAct
                              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

Can you try adding parenthesis for select like

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 hstT.NumUsuarioEmisor=@UsuarioAct AND HstT.NumZona=@ZonaAct AND DATEDIFF(day,@par_Fecha,HstT.FechaHoraTicket)=0 GROUP BY IdArticulo, Precio ORDER BY IdArticulo )

are the variables @UsuarioAct and @ZonaAct getting populated ? Can you try replacing the same with the one when you tried in manual query?

Answer 2

Are you opening the cursor?

Answer 3

Have the variables @UsuarioAct and @ZonaAct been declared as the correct data types?

Maybe an implicit conversion is subtly changing the meaning of the data?

Answer 4

Since you get @@FETCH_STATUS = -1 initially on the inner cursor, I would conclude that your SELECT statement returns no rows. You do mention that you get results when executing the query, but please try the following: the content of the first BEGIN ... END block replace with following code only and see if you do get result sets:

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

and check if you get any results. If you do not, then remove some of the filters in WHERE clause...

Answer 5

I've found it!

If you read the Outer cursor declaration:

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

The order is NumUsuario, ZonaAsignada, IdUnico

But later, in the first FETCH:

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

I have changed the order, the correct is @UsuarioAct, @ZonaAct, @IdUnicoAct.

So, the User (UsuarioAct) and Zone (ZonaAct) data was wrong.

Thank you all by your tips.