Store procedure is not working correctly

Question

Store procedure is not working correctly

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I created a StoreProcedure named, CancelNote, which, by using cursors, returns the products to the stock, passes the status of the item to canceled. It is working, however, not setting the "status" value in the [status] column. If someone can help me!

This is the script 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

sql sql-server stored-procedures

asked by anonymous 30.05.2017 / 21:12

2 answers

error in the sublime Problem with imagebuttom in listview

score 0 · Answer 1

From what I could see, you're doing a DELETE before, and then an INSERT, so depending on how the data is, that does an UPDATE instead.

However, the INSERT is being done with the fixed NUMNF in the value of 100100, besides being in the wrong syntax, I believe that 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');

Correct the INSERT to fit the required columns.

score 0 · Answer 2

I think the code needs to be rewritten.

The cVendas cursor is declared to read row in the NOTAFISCAL table and returns the DATA , VALORNOTA , and MAT columns. In the first FETCH the cursor usage is correct, loading the respective values in the variables @DATA, @VALOR and @MAT. In the FETCH internal to the loop (WHILE statement), it loads the variables @CB and @QTDITEM which, it seems to me, has no relation to the columns DATA , VALORNOTA and MAT .

Another perceived error is that the UPDATE statement inside the loop has the following constraint in the WHERE clause:

WHERE CB = @CB

However, the @CB variable is unmarked for it, in the first run; that is, it is NULL.

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

With the information provided so far, this is a code suggestion draft for invoice cancellation.

-- 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