Stored procedure to insert three sql statements with transaction

Question

I have an invoice which is stored in two tables, first table has the general information of the invoice like invoice number,date,and so on .. And second table has the details of items purchased in the invoice like item name,quantity, and so on...
I want to create a stored procedure to first insert the general information of the invoice then loop through the items of the invoice which will be in datagridview rows to insert them one by one in the invoice details table.And in each detail insertion the quantity of the items in inventory are updated so this making my stored procedure handles 3 sql queries.
I've written a stored procedure but I'm stuck in the part of writing the loop statement in the stored procedure it self .. Can any one help!

Create proc [dbo].[AddNewSellDetails]
@invoiceNo int,
@invoice_date date,
@SELL_ID int,
@PRO_ID int,
@QUANTITY varchar(10),
@UNIT nvarchar(15),
@PRICE varchar(20),
@AMOUNT varchar(20),
@DISCOUNT varchar(20),
@FINAL_TOT varchar(30)
as
begin transaction 
begin try

INSERT INTO Sell
(ID,sellDate) values (@invoiceNo,@invoice_date)

INSERT INTO Sell_Details
           (Sell_ID
           ,Product_ID
           ,Quantity
           ,Unit
           ,Price
           ,Amount
           ,Discount
           ,Final_Tot)   
     VALUES
           (@SELL_ID
           ,@PRO_ID
           ,@QUANTITY
           ,@UNIT
           ,@PRICE
           ,@AMOUNT
     ,@DISCOUNT
     ,@FINAL_TOT)

update Product set Product.Quantity = Product.Quantity - CONVERT(float,@QUANTITY)
where Product.Product_ID=@PRO_ID

commit transaction 

end try
begin catch
rollback transaction 
end catch

Don't loop. Use a table valued parameter instead. Examples [here](https://stackoverflow.com/questions/30817019/how-to-parse-a-varchar-passed-to-a-stored-procedure-in-sql-server/30817115#30817115), [here](https://stackoverflow.com/questions/31965233/adding-multiple-parameterized-variables-to-a-database-in-c-sharp/31965525#31965525), [here](https://stackoverflow.com/questions/41875206/how-to-pass-a-string-larger-than-200-character-to-a-stored-procedure-via-param/41875699#41875699) and [there](https://stackoverflow.com/a/11105413/3094533). — Zohar Peled, Apr 02 '18 at 13:25
This is a another link about "User defined Table types" http://www.sqlservercentral.com/blogs/steve_jones/2012/09/19/creating-a-user-defined-table-type/ — Antonio Avndaño Duran, Apr 02 '18 at 14:09
@Mohammed Shfq, If you will face any problem to run my query statement, please let me. Thanks. — Emdad, Apr 02 '18 at 16:57

score 1 · Answer 1 · answered Apr 02 '18 at 14:13

You need to create table value parameter like following

CREATE TYPE ProductDetails AS TABLE
(
        ProductID INT,
        QUANTITY VARCHAR(10),
        UNIT NVARCHAR(15),
        PRICE DECIMAL(18,2),
        AMOUNT DECIMAL(18,2),
        DISCOUNT DECIMAL(18,2),
        FINAL_TOT INT  
)
GO

Then create procedure like following

CREATE PROCEDURE USP_AddNewSellDetails (@invoiceNo INT,@invoice_date DATE, @Produt_Detils ProductDetails READONLY)
AS
BEGIN
    BEGIN TRANSACTION
    INSERT INTO Sell
    (ID,sellDate) values (@invoiceNo,@invoice_date)

    INSERT INTO Sell_Details (Sell_ID ,Product_ID)   
    SELECT @invoiceNo, ProductID, QUANTITY ,UNIT ,PRICE ,AMOUNT ,DISCOUNT ,FINAL_TOT FROM @Produt_Detils

    DECLARE ProductDetilsCursor CURSOR FOR
        SELECT ProductID, QUANTITY FRM @Produt_Detils ORDER BY ProductID

        OPEN ProductDetilsCursor
        FETCH NEXT FROM ProductDetilsCursor into @ProductID,@QUANTITY

        WHILE ( @@FETCH_STATUS = 0)
        BEGIN
            UPDATE Product set Product.Quantity = Product.Quantity - CONVERT(float,@QUANTITY)
            WHERE Product.Product_ID=@ProductID

            FETCH NEXT FROM ProductDetilsCursor into @ProductID,@QUANTITY
        END
    CLOSE ProductDetilsCursor
    DEALLOCATE ProductDetilsCursor

    COMMIT TRANSACTION
END

Prepare table value parameter before run procedure like following

DECLARE @PD ProductDetails;
INSERT @PD VALUES (1,2,'KG',100,200,25,1),(2,5,'PC',50,250,20,6)

Then execute procedure like following

EXEC USP_AddNewSellDetails 100,'2-Apr-2018',@PD

Stored procedure to insert three sql statements with transaction

1 Answers1