CODEWITHSUNDEEP
sqlsql-serverstored-procedures
testus
testus

Reputation: 183

Stored procedure to insert a list of different types of items in to a table

I have following two tables 

CREATE TABLE Orders
(
OrderID int IDENTITY NOT NULL,
StaffID int NOT NULL,
TotalPrice money NOT NULL,
OrderDateTime dateTime NOT NULL

PRIMARY KEY (OrderID),
FOREIGN KEY (StaffID)
REFERENCES Staff(StaffID)
)


CREATE TABLE OrderDetails
(
OrderDetailID int IDENTITY NOT NULL,
OrderID int NOT NULL,
ItemID int,
ExtrasID int,
ItemQuantity int,
ExtrasQuantity int

PRIMARY KEY (OrderDetailID)
FOREIGN KEY (OrderID)
REFERENCES Orders(OrderID),
FOREIGN KEY (ExtrasID)
REFERENCES Extras(ExtrasID),
FOREIGN KEY (ItemID)
REFERENCES Item(ItemID)
)

I would like to create a stored procedure which will create a new order by inserting data in to both tables. This procedure should take the parameters for the StaffID, TotalPrice and the bought products. Products can be a many Items, many Extras or both.

Is there any way I can have a list of ItemID's and a list of ExtrasID's as the parameters which will then be inserted in to the OrderDetails table correctly?

Upvotes: 0

Views: 250

Answers (1)

M.Ali
M.Ali

Reputation: 69524

Create a type first

CREATE TYPE dbo.ty_Product_Orders AS Table
(
 ItemID         INT
,ExtrasID       INT
,ItemQuantity   INT
,ExtrasQuantity INT
)
GO

Procedure Definition

Now make your procedure accept a parameter of that type. 

CREATE PROCEDURE usp_Place_Order
 @StaffID        INT
,@TotalPrice     MONEY
,@Order          dbo.ty_Product_Orders READONLY
,@OrderPlaced    BIT = 0 OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

 -- table variable to hold the identity values 
  DECLARE @OrderDetails TABLE 
  (
     OrderID        INT,
     ItemID         INT
    ,ExtrasID       INT
    ,ItemQuantity   INT
    ,ExtrasQuantity INT
  )

BEGIN TRY

BEGIN TRANSACTION;

-- Insert Orders

INSERT INTO Orders (StaffID , TotalPrice , OrderDateTime)
OUTPUT inserted.OrderID , inserted.ItemID ,inserted.ExtrasID ,
       inserted.ItemQuantity ,inserted.ExtrasQuantity 
        INTO  @OrderDetails(OrderID , ItemID , ExtrasID 
                            ,ItemQuantity ,ExtrasQuantity)
SELECT @StaffID , @TotalPrice , GETDATE()
FROM @Order


-- insert OrderDetails

INSERT INTO OrderDetails ((OrderID , ItemID , ExtrasID  ,ItemQuantity ,ExtrasQuantity)
SELECT OrderID , ItemID , ExtrasID,ItemQuantity ,ExtrasQuantity
FROM @OrderDetails

  COMMIT TRANSACTION;
END TRY

BEGIN CATCH
 IF (@@TRANCOUNT <> 0)
    ROLLBACK TRANSACTION;

  -- Other error logging here 

END CATCH

END

Upvotes: 1

Related Questions