Board index » delphi » Insert with duplicate key does not create an error in SP, SQL Server 2000

Insert with duplicate key does not create an error in SP, SQL Server 2000

Of course there is an error, but you're not propagating it. The fact that
@@Error is not zero means that there is an error.

Try to make an raiserror if you want to "see" it as an exception.

"Marius Horak" <marius.ho...@tnsofres.com> escribi en el mensaje
news:3d5d138a$1@dnews...

Quote
> The below procedure a duplicate invoice number is created but INSERT INTO
> INVOICES does not create an error. INVOICENUM is a PK on INVOICES table.

> @@ERROR is 0 after SP_GETINVOICENUMBER and after INSERT so checking for
> errors in SP does not help. SCOPE_IDENTITY is 0 as INSERT fails.

> If I remove

> EXECUTE SP_GETINVOICENUMBER @INVOICETYPE,@INVOICESORT,@INVNUM OUTPUT

> and replace with SET @INVNUM='IN20801414' where I know that such invoice
> exists
> INSERT will produce an error.

> So for some reason the presence of EXECUTE SP_GETINV... supresses errors.

> Any ideas what's going on?

> Thanks

> Mariusz

> /* ===== SP_INSERTINVOICE =============================== */
> CREATE PROCEDURE SP_INSERTINVOICE
> @COMPANYID INTEGER,
> @INVOICETYPE INTEGER,
> @INVOICEDATE DATETIME,
> @INVOICESTATE INTEGER,
> @PRINTSTATUS INTEGER,
> @EXPORTSTATUS INTEGER,
> @NOTES TEXT,
> @INVOICESORT INTEGER,
> @PONUMBER VARCHAR(20),
> @INVOICEDBY VARCHAR(40),
> @NETT MONEY,
> @VAT MONEY,
> @EXTERNAL INTEGER,
> @INVOICEORIGINAL INTEGER,
> @INVNUM VARCHAR(20) = NULL OUTPUT
> AS

> DECLARE @RET_VAL INT

> EXECUTE SP_GETINVOICENUMBER @INVOICETYPE,@INVOICESORT,@INVNUM OUTPUT

> IF @@ERROR<>0 BEGIN
>   RETURN -@@ERROR
> END

> INSERT INTO INVOICES
> (
> INVOICENUM, COMPANYID, INVOICETYPE, INVOICEDATE,
> INVOICESTATE, PRINTSTATUS, EXPORTSTATUS, NOTES,
> INVOICESORT, PONUMBER, INVOICEDBY, NETT, VAT,
> EXTERNAL, INVOICEORIGINAL
> )
> VALUES
> (
> @INVNUM, @COMPANYID, @INVOICETYPE, @INVOICEDATE,
> @INVOICESTATE, @PRINTSTATUS, @EXPORTSTATUS, @NOTES,
> @INVOICESORT, @PONUMBER, @INVOICEDBY, @NETT, @VAT,
> @EXTERNAL, @INVOICEORIGINAL
> )

> IF @@ERROR<>0
>   SET @RET_VAL=-@@ERROR
> ELSE
>   SET @RET_VAL = SCOPE_IDENTITY()
> RETURN @RET_VAL

 

Re:Insert with duplicate key does not create an error in SP, SQL Server 2000


Quote
"FDS" <Fel...@spamfreejazzfree.com> wrote in message news:3d5d20c1@dnews...
> Of course there is an error, but you're not propagating it.
> The fact that @@Error is not zero means that there is an error.

What fact are you talking about?

@@ERROR is ALWAYS zero

Marius

Re:Insert with duplicate key does not create an error in SP, SQL Server 2000


Thank you, Andy.

Solved my problem.
It had nothing to do with the code in SP (or very, very little).

Marius

Other Threads