How to write a store procedure that uses values from the current and previous row?

Bruno, the problem you propose seems to me similar (but not identical) to the issue of bank account statement, where to calculate the balance of the day one must obtain the balance of the previous day, add the credits of the day and subtract the debts of the day.

The mass of data you posted as an example does not seem to me useful to test the proposed solutions, since most of the columns that enter the formula are zeroed. So I've chosen to create your own data mass for testing, which is at the end of this answer.

PREMISES
In the sample analysis of the table, it is observed that whenever the value of the referencia column is changed, the sequential counter of the id2 column is reset. I assume then that the stock value must be recalculated individually for each value of the referencia column, with the id2 column sequenced. That is why, in the proposed solutions, I use the pair ( referencia , id2 ) as the search key.

In the comments you mention that the calculation of nStock should start from the second line; I understand that it is from each value of the referencia column. So I'm assuming that the value of column nStock , whenever id2 is 1 (first row), is correct.

SOLUTION 1

The first solution I propose uses Recursive CTE . The code is very simple.

-- código #1 v3
with estoqueAtual as (
SELECT id, dataTmp, referencia, id2, nStock, nProduz, necessidade,
       novoStock= nStock
  from SeguinteTabela
  where id2 = 1
union all
SELECT T2.id, T2.dataTmp, T2.referencia, T2.id2, 
       T2.nStock, T2.nProduz, T2.necessidade,
       (T1.novoStock + T2.nProduz - T2.necessidade)
  from estoqueAtual as T1
       inner join Seguintetabela as T2 
                  on T1.referencia = T2.referencia and T1.id2 = (T2.id2 -1)
)
SELECT id, Convert(char(10), dataTmp, 103) as dataTmp, referencia, id2, 
       nStock, nProduz, necessidade, novoStock
  from estoqueAtual
  --where dataTmp >= 
  order by referencia, id2;

For you to test with the database table, replace the text SeguinteTabela with the actual table name.

Here is the result of using the created mass of data.

To update the table, code # 1 is slightly modified, replacing the final SELECT with UPDATE:

-- código #3 v2
with estoqueAtual as (
SELECT id, dataTmp, referencia, id2, nStock, nProduz, necessidade,
       novoStock= nStock
  from SeguinteTabela
  where id2 = 1
union all
SELECT T2.id, T2.dataTmp, T2.referencia, T2.id2, T2.nStock, T2.nProduz,
       T2.necessidade, (T1.novoStock + T2.nProduz - T2.necessidade)
  from estoqueAtual as T1
       inner join Seguintetabela as T2 
                  on T1.referencia = T2.referencia and T1.id2 = (T2.id2 -1)
)
UPDATE T4
  set nStock= T3.novoStock
  --output deleted.id, deleted.nStock, inserted.nStock
  from estoqueAtual as T3
       inner join SeguinteTabela as T4 on T3.id = T4.id
  where T4.nStock <> T3.novoStock
        --and T3.dataTmp >=

For you to test with the database table, replace the text SeguinteTabela with the actual table name.

SOLUTION 2

The second solution uses another approach. The result is the same as the # 1 code.

-- código #4 v2
SELECT T1.id, T1.dataTmp, T1.referencia, T1.id2, 
       T1.nStock, T1.nProduz, T1.necessidade,
       novoStock= case when T1.id2 = 1
                       then T1.nStock
                       else ((SELECT nStock 
                                from SeguinteTabela as T2
                                where T2.referencia = T1.referencia
                                      and T2.id2 = 1) +
                             (SELECT Sum(nProduz - necessidade) 
                                from SeguinteTabela as T2
                                where T2.referencia = T1.referencia
                                      and T2.id2 > 1
                                      and T2.id2 <= T1.id2)
                            )
                  end
  from SeguinteTabela as T1
  --where T1.dataTmp >=
  order by T1.referencia, T1.id2;

To update, code # 4 is transformed as CTE, then:

-- código #5 v2
with estoqueAtual as (
SELECT T1.id, 
       novoStock= case when T1.id2 = 1
                       then T1.nStock
                       else ((SELECT nStock 
                                from SeguinteTabela as T2
                                where T2.referencia = T1.referencia
                                      and T2.id2 = 1) +
                             (SELECT Sum(nProduz - necessidade) 
                                from SeguinteTabela as T2
                                where T2.referencia = T1.referencia
                                      and T2.id2 > 1
                                      and T2.id2 <= T1.id2)
                            )
                  end
  from SeguinteTabela as T1
  --where dataTmp >=
)
UPDATE T4
  set nStock= T3.novoStock
  --output deleted.id, deleted.nStock, inserted.nStock
  from estoqueAtual as T3
       inner join SeguinteTabela as T4 on T3.id = T4.id
  where T4.nStock <> T3.novoStock;

Here is the code that generates the mass of data for testing.

-- código #2
CREATE TABLE SeguinteTabela (
  id int, dataTmp date, referencia varchar(20), 
  nProduz int, nStock int, id2 int, necessidade int);

set dateformat dmy;
truncate table SeguinteTabela;
INSERT into SeguinteTabela values 
  (115262, '30-01-2017', 'VL03280203', 20, 100, 1, 5),
  (115263, '30-01-2017', 'VL03280203', 30, 0, 2, 10),
  (115264, '30-01-2017', 'VL03280203', 25, 0, 3, 100),
  (115265, '31-01-2017', 'VL03280203', 35, 0, 4, 0),
  (115266, '31-01-2017', 'VL03280203', 40, 0, 5, 120),
  (115267, '28-01-2017', 'VL03280303', 20, 0, 1, 0),
  (115268, '29-01-2017', 'VL03280303', 30, 0, 2, 10),
  (115269, '30-01-2017', 'VL03280303', 20, 0, 3, 18);
go

To perform the process you want, you can use a cursor to scroll through the rows and select the previous value, thus updating the new value:

create procedure atualizar
as
begin
  declare @id          int,
          @nStock      int,
          @nProduz     int,
          @necessidade int;

  set nocount on;

  declare cursorEstoque cursor local fast_forward for
    select tb.id,
           isnull(tb.nProduz, 0),
           isnull(tb.necessidade, 0)
      from tabela tb
     order by tb.id;
  open cursorEstoque
  fetch next from cursorEstoque into @id, @nProduz, @necessidade
  while @@fetch_status = 0
  begin
    -- Pega o valor do último registro
    select top 1 @nStock = isnull(tb.nStock, 0) -- Garante que será calculado caso haja linha anterior
      from tabela tb
     where tb.id < @id
     order by tb.id desc;

    if @nStock is not null -- Será nulo caso não tenha linha anterior
    begin
      set @nStock = @nStock + @nProduz - @necessidade;

      update tb
         set nStock = @nStock
        from tabela tb
       where tb.id = @id;
    end;

    fetch next from cursorEstoque into  @id, @nProduz, @necessidade;
  end;
  close cursorEstoque;
  deallocate cursorEstoque;
end;
go

Another way without needing select inside the body of cursor :

create procedure atualizar
as
begin
  declare @id             int,
          @nStock         int,
          @nProduz        int,
          @necessidade    int,
          @nStockAnterior int;

  set nocount on;

  declare cursorEstoque cursor local fast_forward for
    select tb.id,
           isnull(tb.nStock, 0),
           isnull(tb.nProduz, 0),
           isnull(tb.necessidade, 0)
      from tabela tb
     order by tb.id;
  open cursorEstoque
  fetch next from cursorEstoque into @id, @nStock, @nProduz, @necessidade
  while @@fetch_status = 0
  begin
    if @nStockAnterior is not null -- Será nulo caso não tenha linha anterior
    begin
      set @nStock = @nStockAnterior + @nProduz - @necessidade;

      update tb
         set nStock = @nStock
        from tabela tb
       where tb.id = @id;
    end;

    set @nStockAnterior = @nStock;

    fetch next from cursorEstoque into  @id, @nStock, @nProduz, @necessidade;
  end;
  close cursorEstoque;
  deallocate cursorEstoque;
end;
go

A third way is to use a variable of type table to store the data with a sequential generated from sorting in the table taking into account id :

create procedure atualizar
as
begin
  declare @dados table(sequencia      int,
                       id             int,
                       nStock         int,
                       nProduz        int,
                       necessidade    int);

  set nocount on;

  insert into @dados(sequencia,
                     nStock,
                     nProduz,
                     necessidade)
  select row_number() over(order by tb.id),
         isnull(tb.nStock, 0),
         isnull(tb.nProduz, 0),
         isnull(tb.necessidade, 0)
    from tabela tb
   order by tb.id;

  update tb
     set tb.nStock = tba.nStock + tb.nProduz - tb.necessidade;
    from tabela tb
         inner join @dados tba on tba.sequencia = (tb.sequencia - 1) -- Pega o anterior, afinal o sequencial não pula nenhum número
end;
go

Why use the table and not just something like id - 1 ? Because with id - 1 if any of the lines are deleted, id will have "holes", which will make the logic go wrong at some point.

Note: To use the tables, change the name in procedures to the name you are using in your database.

Using cursors in this case is a procedural way of thinking about working with SQL which can make your procedure slow depending on how this cursor is written. It is best to remember the set theory and think of its unique table as two tables where the intersection or JOIN is done with id = id-1 .

MS SQL SERVER has a very interesting feature that is UPDATE FROM if you want to update the data table.

I would do a StoredProcedure with the following queries. Use them as you need them. I am giving the name of the source table of tabela_dados .

Inquiry:

-- Para simples consulta

SELECT nStock = RowAnterior.nStock + RowActual.nProduz - RowActual.necessidade
FROM tabela_dados AS RowActual
INNER JOIN tabela_dados AS RowAnterior ON (RowActual.id = RowAnterior.id-1)

Update:

-- Para atualizar a tabela

UPDATE RowActual SET RowActual.nStock = RowAnterior.nStock + RowActual.nProduz - RowActual.necessidade
FROM tabela_dados AS RowActual
INNER JOIN tabela_dados AS RowAnterior ON (RowActual.id = RowAnterior.id-1)

with estoqueAtual as (
SELECT id_Matrix, dataTmp, referencia, id2, nStock, nProduz,         necessidade,turno,
       novoStock= nStock
  from Matrix
  where dataTmp=(select dateadd(dd,0, cast(getdate() as date))) and turno=1
union all
SELECT T2.id_Matrix, T2.dataTmp, T2.referencia, T2.id2, T2.nStock, T2.nProduz,
       T2.necessidade,t2.turno, (T1.novoStock + T2.nProduz - T2.necessidade)
  from estoqueAtual as T1
       inner join Matrix as T2 
                  on T1.referencia = T2.referencia and T1.id2 = (T2.id2 -1)
)
UPDATE T4
  set nStock= T3.novoStock
  --output deleted.id, deleted.nStock, inserted.nStock
  from estoqueAtual as T3
       inner join Matrix as T4 on T3.id_Matrix = T4.id_Matrix
  where T4.nStock <> T3.novoStock and T3.dataTmp >=(select dateadd(dd,0,     cast(getdate() as date)))
  option (maxrecursion 0)
  ; 

Here is the final code was to just add the date and pass the need for shift = 2 which had not yet put in the bd ... Right now it works perfectly, Thank you!