CODEWITHSUNDEEP
sql-server
tiago montoya
tiago montoya

Reputation: 23

Need assistance with creating a stored procedure which inserts into two tables

I'm trying to create a simple stored procedure that has two insert queries. The problem is its inserting duplicates of the same record which im inserting from a javafx desktop application. here is the procedure

ALTER PROCEDURE [dbo].[insertIntoInvoicesAndInvLines]
    (@invoiceNumber AS NVARCHAR(MAX),
     @supplierAccountCode AS NVARCHAR(MAX),
     @theDate AS DATETIME,
     @dealNoteNumber AS NVARCHAR(MAX),
     @orderNumber AS VARCHAR(MAX),
     @quantity AS FLOAT,
     @units AS NVARCHAR(MAX),
     @packSize AS FLOAT,
     @cPaid AS BIT)
AS
BEGIN
    INSERT INTO Invoices (C_invno, C_supno, D_invDate, C_delno, C_OrderNo, C_Paid)
    VALUES (@invoiceNumber, @supplierAccountCode, @theDate, @dealNoteNumber, @orderNumber, @cPaid);

    INSERT INTO InvLine (N_itemQty, C_ItemUnits, N_PkSize)
    VALUES (@quantity, @units, @packSize);
END

Upvotes: 1

Views: 48

Answers (1)

dmgx0
dmgx0

Reputation: 131

You can add condition into your procedure before each insert statement. I'll assume that the invoice number column is unique.

There is a simple solution:

IF NOT EXISTS(SELECT 1 FROM Invoices WHERE C_invno = @invoiceNumber)
BEGIN
    -- insert into Invoices
END

But you need review your tables structure - if you want consistency for you DB, you can add some constraints (e.g. unique) - it will generate errors if you try to insert some duplicates.

Upvotes: 1

Related Questions