Store Procedure not working properly

Question

Galera created a Storeprocedure called, Cancelarnota, that through the use of cursors, back the products to the stock, pass the status of the item to canceled. This working, however, is not setting in the column [status] the value of "inactive". If anyone can help me!

This is the scrip I created:

execute sp_CancelarNota 200200

create procedure sp_CancelarNota(@notafiscal integer)
AS
DECLARE @DATA DATE
DECLARE @VALOR DECIMAL(10,2)
DECLARE @MAT INT
DECLARE @CB INT
DECLARE @QTDITEM DECIMAL(10,2)
DECLARE @STATUS CHAR(1)

DECLARE cVendas CURSOR
FAST_FORWARD 
FOR SELECT DATA, VALOrNOTA, MAT FROM NOTaFISCAL
WHERE NUMNF = @notafiscal
OPEN cVendas
FETCH NEXT FROM cVendas INTO @DATA, @VALOR, @MAT
WHILE @@FETCH_STATUS = 0

BEGIN
    UPDATE PRODUTO SET QTDeATUAL = QTDeATUAL+@QTDITEM
    WHERE CB = @CB
    FETCH NEXT FROM cVendas INTO @CB, @QTDITEM
END

DELETE FROM NOTaFISCAL WHERE
NOTaFISCAL.NUMNF = @notafiscal
DELETE FROM ITENsNOTA WHERE
ITENsNOTA.NUMNF = @notafiscal
INSERT INTO NOTaFISCAL (STATUS) WHERE NUMNF = 100100 VALUES('INATIVO');

CLOSE cVendas
DEALLOCATE cVendas

Answer 1

From what I could see, you are doing a DELETE before, and then an INSERT, so depending on how the data is, it makes the turn of an UPDATE.

However, the INSERT is being done with a fixed NUMNF of 100100, besides being in the wrong syntax, I believe it should be receiving the @notafiscal variable. So the code snippet should be changed to this below:

INSERT INTO NOTaFISCAL (NUMNF, STATUS) VALUES (@notafiscal, 'INATIVO');

Fix INSERT to fit required columns.

Answer 2

It seems to me that the code needs to be rewritten.

The cursor cVendas is declared to read row in table NOTAFISCAL and return the columnsDATA, VALORNOTA and MAT. In the first FETCH the use of the cursor is correct, loading the respective values in the variables @DATA, @VALUE and @MAT. In the FETCH internal to the loop (WHILE instruction), it loads the variables @CB and @QTDITEM which, it seems, has no relation to the columns DATA, VALORNOTA and MAT.

Another perceived error is that the UPDATE statement, within the loop, has the following restriction in the WHERE clause:

WHERE CB = @CB

However, the @CB variable is not marked for it at the first execution; that is, it is NULL.

It is also strange that the line containing the invoice is deleted from the NOTAFISCAL table if it is necessary to change the STATUS to 'INACTIVE'.

---

With the information provided so far, here is draft code suggestion for cancellation of invoice.

-- código #1 v2
CREATE PROCEDURE CancelarNota
       @notafiscal integer

begin

-- analisa parâmetro
IF @notafiscal is null 
  return -1;  -- erro: número de nota fiscal não informado

IF not exists (SELECT * from NOTAFISCAL
                 where NUMNF = @notafiscal)
  return -2;  -- erro: não existe a nota fiscal

IF (SELECT STATUS from NOTAFISCAL
      where NUMNF = @notafiscal) = 'INATIVO'
  return -3;  -- erro: nota fiscal está inativa

--
BEGIN TRANSACTION;

-- estorna quantidade de itens de cada produto
UPDATE P
  set QTDEATUAL= QTDEATUAL + IN.QTDEVEND
  from PRODUTO as P
       inner join ITENSNOTA as IN on IN.CB = P.CB
  where IN.NUMNF = @notafiscal;

-- apaga itens da venda
DELETE ITENSNOTA 
  where NUMNF = @notafiscal;

-- marca a nota fiscal como inválida
UPDATE NOTAFISCAL
  set STATUS = 'INATIVO'
  where NUMNF = @notafiscal;

COMMIT;

return 0;   -- ok!
end;
go