CODEWITHSUNDEEP
sql-servert-sqlstored-procedurescursor
Splendonia
Splendonia

Reputation: 1369

Cursor seems to be fetching only one record

I'm trying to do this a stored procedure that for each Location = "Ubicacion" (counts the number of fixed asssets = Ubicacion, sums the amount of money those assets are worth, and sums the amount of money to depreciate of those assets) for the assets currently placed in that location. The problem is the cursor seems to be only fetching one time

Query for Cursor:

SELECT DISTINCT (ubicacion) FROM
         (SELECT activo, ubicacion, Fecha_Ubicacion, row_number() OVER (
            partition BY activo ORDER BY abs(datediff(dd, Fecha_Ubicacion, getdate()))
          ) AS RowNum FROM [SISACT].ACTIVO_UBICACION) someAlias WHERE RowNum = 1

Result:

Ubicacion
----------
1114
4450
4353

It should be fetching 3 times. One for each record in "Locations", and only the most recent records in locations.

    ALTER PROCEDURE [SISACT].[resume_activos]
AS
BEGIN
  DECLARE @u CHAR(8);
  DECLARE @p VARCHAR(8);
  DECLARE @num_activos INT = 0;
  DECLARE @monto_activos FLOAT = 0;
  DECLARE @saldo_dep_activos FLOAT = 0;

  DECLARE U CURSOR STATIC
    FOR SELECT DISTINCT (ubicacion) FROM
         (SELECT activo, ubicacion, Fecha_Ubicacion, row_number() OVER (
            partition BY activo ORDER BY abs(datediff(dd, Fecha_Ubicacion, getdate()))
          ) AS RowNum FROM [SISACT].ACTIVO_UBICACION) someAlias WHERE RowNum = 1
        OPEN U
    FETCH NEXT FROM U INTO @u
    WHILE @@FETCH_STATUS = 0
            BEGIN
        SELECT @num_activos = COUNT(a.Activo), @monto_activos = SUM(a.Costo_adquisicion), @saldo_dep_activos = SUM(a.Saldo_a_depreciar) FROM [SISACT].ACTIVOS_FIJOS a, [SISACT].UBICACIONES ub, (SELECT activo, ubicacion, Fecha_Ubicacion, row_number() OVER (
            partition BY activo ORDER BY abs(datediff(dd, Fecha_Ubicacion, getdate()))
          ) AS RowNum FROM [SISACT].ACTIVO_UBICACION) ab WHERE RowNum = 1 AND ub.Ubicacion = @u AND a.Activo = ab.Activo AND ab.Ubicacion = @u

           PRINT 'Ubicacion: ' +@u +' Num activos: ' + CONVERT(VARCHAR, @num_activos) + ' monto activos: ' + CONVERT(VARCHAR, @monto_activos) + ' saldo activos depreciados: '+ CONVERT(VARCHAR, @saldo_dep_activos)

                FETCH NEXT FROM U INTO @u
            END
    CLOSE U
    DEALLOCATE U

END

The "Print" statement only shows once in the "Message" tab with the first location and the calculation of what i've said earlier, plus it's not returning anything, I'd like it to return something like this

Ubicacion | Num_Act | Monto_Act | Saldo_dep_Act
------------------------------------------------
4453      |    2    | 5787.65   | 332.247

I'm new to T-SQL so forgive me if i'm doing something really stupid very wrong, I've only done a SP with a cursor before and it worked fine (and it didn't returned anything).

What am I doing wrong?

Thank you in advance.

Upvotes: 1

Views: 2368

Answers (1)

Pablo Romeo
Pablo Romeo

Reputation: 11396

It is actually doing the calculations correctly. I changed your SP to accumulate the output in a varchar(max) and only output at the end:

CREATE PROCEDURE [resume_activos]
AS
BEGIN
  DECLARE @u VARCHAR(8);
  DECLARE @p VARCHAR(8);
  DECLARE @allOutput VARCHAR(MAX) = '';
  DECLARE @num_activos INT = 0;
  DECLARE @monto_activos FLOAT = 0;
  DECLARE @saldo_dep_activos FLOAT = 0;

  DECLARE U CURSOR STATIC
    FOR SELECT DISTINCT (ubicacion) FROM
         (SELECT activo, ubicacion, Fecha_Ubicacion, row_number() OVER (
            partition BY activo ORDER BY abs(datediff(dd, Fecha_Ubicacion, getdate()))
          ) AS RowNum FROM ACTIVO_UBICACION) someAlias WHERE RowNum = 1
        OPEN U
    FETCH NEXT FROM U INTO @u
    WHILE @@FETCH_STATUS = 0
            BEGIN
        SELECT @num_activos = COUNT(a.Activo), @monto_activos = SUM(a.Costo_adquisicion), @saldo_dep_activos = SUM(a.Saldo_a_depreciar) FROM ACTIVOS_FIJOS a, UBICACIONES ub, (SELECT activo, ubicacion, Fecha_Ubicacion, row_number() OVER (
            partition BY activo ORDER BY abs(datediff(dd, Fecha_Ubicacion, getdate()))
          ) AS RowNum FROM ACTIVO_UBICACION) ab WHERE RowNum = 1 AND ub.Ubicacion = @u AND a.Activo = ab.Activo AND ab.Ubicacion = @u

           SET @allOutput = @allOutput + char(10) + char(13) + 'Ubicacion: ' +@u +' Num activos: ' + CONVERT(VARCHAR, @num_activos) + ' monto activos: ' + CONVERT(VARCHAR, @monto_activos) + ' saldo activos depreciados: '+ CONVERT(VARCHAR, @saldo_dep_activos)

                FETCH NEXT FROM U INTO @u
            END
    CLOSE U
    DEALLOCATE U
  SELECT @allOutput

END

And the output is:

Ubicacion: 1114 Num activos: 2 monto activos: 2781.15 saldo activos depreciados: 53.1104
Ubicacion: 4450 Num activos: 1 monto activos: 4553 saldo activos depreciados: 126.472
Ubicacion: 4453 Num activos: 2 monto activos: 5787.65 saldo activos depreciados: 332.247

You can see it working here: http://sqlfiddle.com/#!3/bce40/1/0

If you prefer to have the result as an actual table, just createa temporary table, and insert the partial result on each iteration. Then at the end, select from it to get your full set of results.

Upvotes: 1

Related Questions