Create Stored Procedure to Insert Multiple Rows in SQL Server

bulk-insertdatabase-designsql-server-2012stored-procedurestransaction-log

I am new to Databases and Design and needs some help on a INSERT issue.
I want to create a procedure that will insert multiple values to different tables, I need to make sure it stays atomic so I need to use transact in the procedure .

Invoice_Table

–Invoice_ID (PK)

–Purchased_Date

Fruit_Table

–Fruit_ID (PK)

–Fruit_Name

Driver_Table

–Driver_ID (PK)

–Driver_Name

–Driver_LastName

Invoice_details_Table

–Invoice_ID FK

–Driver_ID FK

–Fruit_ID FK

–Quantity

–Amount

The reason why I created a junction table( Invoice_Details_Table) is for the reason that some invoices will have more than one driver with one or more fruits.

For example a trip was done so we need to create an invoice(1). Two drivers went on this trip and delivered Strawberries and Apples.

Trip number two so invoice(2) needs to be created. This delivery was with one driver and only apples were delivered.

The problem that I am having is how to create a procedure that will update the invoice table and the Invoice_Detail_Table and rollback if something happens in the insert process. Also how can I pass the driver/fruit parameters since sometimes it will be more than one.

I am using SQL Server 2012.
Any help is much appreciated

Best Answer

So what you want is an insert, an update and a delete procedure for each table. Then you want a "wrapper" procedure that tracks the inserts/updates/deletes and responds accordingly to errors.

A "transaction" would be the batch and "rollbacks" would be explicit. I put these in quotes because you don't want to encompass all of the transactions into a single transaction. You can't nest user transactions and a large single transaction would cause some serious blocking.

Pseudo Code for inserts...

Exec usp_NewInvoice 
      @purchaseDt=@purchaseDt
    , @fruitID   =@fruitID
    , @driverID  =@driverID
    , @qty       =@qty
    , @price     =@price

--parameters are passed with the variable values created by the app

usp_NewInvoice
      @purchaseDt datetime2
    , @fruitID    int
    , @driverID   int
    , @qty        int
    , @price      money

    TRY
    BEGIN
        @ReturnValue= exec usp_InsertInvoice 
                          @purchaseDt=@purchaseDt
                        , @invoiceID=@invoiceID OUTPUT

        if @ReturnValue<>0 
        BEGIN
            --Error handling and rollback
            goto ERROR
        END
    END

    CATCH
    BEGIN
        @ReturnValue<>0 
        BEGIN
            --Error Handling and rollback
            goto ERROR
    END

    BEGIN
        @ReturnValue= exec usp_InsertInvoiceDetail 
                          @invoiceID =@invoiceID
                        , @fruitID   =@fruitID
                        , @driverID  =@driverID
                        , @qty       =@qty
                        , @price     =@price

        if @ReturnValue<>0 
        BEGIN
            --Error handling and rollback
            goto ERROR
        END
    END

    CATCH
    BEGIN
        @ReturnValue<>0 
        BEGIN
            --Error Handling and rollback
            goto ERROR
    END

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

For each operation you are making sure if succeeded. If it doesn't you will need to roll back all previous transactions with other procedures (updates and or deletes). You'll want to do something similar for the updates and deletes.

Related Question