Stored procedure to add a new order which takes a list of products-CodePudding

I have 3 tables: Products, Orders and OrderDetails.

Products

ProductID	ProductName	QtyInStock	CurrentPrice
1	Adidas Shoes	10	100.00
2	Nike Shoes	10	100.00

Orders

OrderID	OrderDate	DeliveryAddress	CurrentPrice
1	27-04-22	10	100.00
2	27-04-22	10	100.00

OrderDetails

OrderID	ProductID	QtyOrdered
1	1	2
1	2	2
2	1	1
2	2	2

I have already created a stored procedure for creating a new Product which is obviously added to the Products table, however, I need to create the following stored procedure for adding a new Order:

Within the stored procedure, you allow a list of products to be passed together with the order number
When the quantity is ordered, please decrease the Quantity in Stock in the Product table

For example:

Order:
- Order: 123
- Order Date: 2022-01-01 09:02:32.232
- Delivery Address: 1 Smith St, Melbourne
Order Items:
- ProductId: 1, Quantity: 10
- ProductId: 6, Quantity: 1
- ProductId: 8, Quantity: 18

I am also required to use a table-valued parameter to complete this stored procedure.

I guess the question I don't know the answer to is how to pass a list of key-value pairs when executing the stored procedure for creating the new order.

Edit: My current attempt Firstly, sorry if this is horribly wrong but here's my current attempt for those asking:

CREATE TYPE OrderInfo as TABLE
(
    OrderID int,
    OrderDate date,
    DeliveryAddress varchar(100),
    ProductID int,
    Quantity int
)

CREATE PROC OrderData
@OrderTVP OrderInfo READONLY
AS
SELECT * FROM @OrderTVP

DECLARE OrderVarParam AS OrderInfo

INSERT INTO @OrderVarParam
    INSERT INTO Orders values(OrderID, OrderDate, DeliveryAddress)
    INSERT INTO OrderDetails values (OrderID, ProductID, Quantity)

EXEC OrderData @OrderVarParam

CodePudding user response：

You're on the right track, but you've got a ways to go yet.

You said this question was "for a job", which sounds like a sort of "take home exam" for a job interview, which creates an ethical problem for people who can help: If we just give you the answer, then we are in some sense helping you to "lie" about your actual level of knowledge, which is presumably what the question is supposed to be checking. Meta discussion on this site generally seems to prefer a "help, but don't solve" approach.

I also have questions about the "schema" you have included in your question.

For example, your Orders table includes a DeliveryAddress column, with sample data that looks like some kind of DeliveryAddressId. But there's no Addresses table.

Also, your Orders table has a CurrentPrice column. But CurrentPrice appears to be something that applies to a Product. If an Order can have multiple OrderDetails, and each OrderDetail can refer to a different Product, how can an Order have a CurrentPrice? Is it meant to be the sum of prices on the lines, perhaps?

But I'm going to continue without answers to these questions, because I'm not going to give you an entire solution and don't need all of the details to do that. I'm going to "help, but not solve".

So, let's go through what you've got:

CREATE TYPE OrderInfo as TABLE
(
    OrderID int,
    OrderDate date,
    DeliveryAddress varchar(100),
    ProductID int,
    Quantity int
)

You are going to create one order.

That order will have a single OrderId, and OrderDate, and DeliveryAddress. But it can have multiple OrderDetails, with each OrderDetail having its own Product and Quantity.

If you pass the OrderId, OrderDate and DeliveryAddress in the table valued parameter, you will be passing these same values multiple times (once per required OrderDetail), which is unnecessary.

Only the ProductId and Quantity need to be columns in the table valued parameter. The other three values can be passed as scalar parameters when we get to the procedure.

This also implies a better name for our type. It's not just "order information in general". it's specifically information about the products on the order. I think OrderedProducts would be a good name for the type.

Per Jonathan's comment below you have specified a Date datatype for the OrderDate, but your question implies you want to be able to have a datetime. We'll fix that in the scalar parameter.

Looking at the next bit:

CREATE PROC OrderData
@OrderTVP OrderInfo READONLY
AS
SELECT * FROM @OrderTVP

In your question you said "I don't know how to pass a list of key-value pairs when executing the stored procedure", but you've actually got that part pretty much right as described above, except for the unnecessary columns. Don't think of the table valued parameter as "key value pairs". We're in a relational database here. It's a table.

Do you get to name the procedure, or have you been told to use the name OrderData? That's a bad name in my opinion. Name things after what they are (as per the OrderedProducts), or what they do. What does this procedure do, in business terms? It creates an order.

Technical point: You currently have all of this in "one script", at least as far as the way you've written it in your question is concerned. if you tried to execute this whole script as a deployment it would fail, because create procedure has to be the first statement in a batch, and you have no batch terminator after your create type statement.

create type OrderedProducts as table ( /* fill in the correct columns here */ );

-- you need a batch terminator to make this a deployable script
go

-- Get into the habit of using "create or alter" for procedures, functions and views
create or alter procedure CreateOrder 
(
   @orderId int, 
   @orderDate datetime, 
   @DeliveryAddress varchar(100),
   @OrderedProducts OrderedProducts readonly
) as begin
   -- typically good practise to include this at the top of a procedure
   -- unless you have a very specific reason not to
   set xact_abort, nocount on; 

   -- do work here

end

From this point on you haven't made much of an attempt. You need to:

update some rows in the Products table, and
insert a new row into the Orders table, and
insert some rows into the OrderDetails table

And you're probably going to want to do it in that order. Why? Well, think about what happens if two users are both placing orders for the same product at the same time. You only have a limited amount of stock available. If one user consumes all remaining quantity, is there any point trying to create the rows in Orders and OrderDetails for the second user?

But you're not doing any of those things. Clearly you do have some idea how to do an insert, because you're inserting into the table valued parameter in your posted code!

Perhaps you are struggling with the idea of inserting or updating multiple rows at the same time? But you can find information on how to do this on the net pretty easily.

What if your procedure successfully does the update of the Products table, but then fails when trying to do the insert into the Orders table? You want everything in this procedure to complete together, or you want everything to fail together. You need to create a "unit of work". You need a transaction, and some way of handling failure appropriately:

create type OrderedProducts as table ( /* ... */ );
go

-- Get into the habit of using "create or alter" for procedures, functions and views
create or alter procedure CreateOrder 
(
   @orderId int, 
   @orderDate datetime, 
   @DeliveryAddress varchar(100),
   @OrderedProducts OrderedProducts readonly
) as begin
   -- typically good practise to include this at the top of a procedure
   -- unless you have a very specific reason not to
   set xact_abort, nocount on; 

   begin try

      begin tran;

      update p
      set    /* you need to change the quantity here */
      from   Products         p
      join   @OrderedProducts op  on  -- ... fill in the rest

      insert Orders (OrderId, OrderDate, /* etc */ )
      values ( /* ... */ );

      insert OrderDetails ( /* ... */)
      select /* ... */
      from   @OrderedProducts op
      join   Products         p on  /* ... you need to get the price */

      commit;
      return 0;

   end try begin catch

      -- any kind of error handling you want to do, 
      -- but explicitly rollback juuuuust in case someone removes the xact_abort later!
      if (@@trancount > 0) rollback;    

   end catch
end

For infomration on patterns for error handling, I know of no better source of information than this series of articles by Erland Sommarskog.

CodePudding user response：

You only need a TVP for the items/details, not the whole order.

The TVP should have a primary key. The other columns should probably also be NOT NULL.
CurrentPrice should be part of the OrderDetails table, not Orders.
Use a transaction to ensure correct rollback.
Products needs a joined update.
Sounds like you want datetime not date for OrderDate.

CREATE TYPE dbo.OrderInfo AS TABLE
(
    ProductID int PRIMARY KEY,
    Quantity int NOT NULL
);

CREATE OR ALTER PROC dbo.InsertOrder
    @OrderDate datetime,
    @DeliveryAddress varchar(100),
    @ItemsTVP OrderInfo READONLY,
    @OrderID int
AS

SET NOCOUNT, XACT_ABORT ON;  -- must have XACT_ABORT if using transaction

BEGIN TRAN;

UPDATE p
SET p.QtyInStock -= i.Quantity
FROM @OrderTVP i
JOIN Products p ON p.ProductID = i.ProductID;

INSERT Orders (OrderID, OrderDate, DeliveryAddress)
VALUES (@OrderID, @OrderDate, @DeliveryAddress);

SET @OrderID = SCOPE_IDENTITY();

INSERT OrderDetails (ProductID, QtyOrdered, CurrentPrice)
SELECT i.ProductID, i.Quantity, p.CurrentPrice
FROM @OrderTVP i
JOIN Products p ON p.ProductID = i.ProductID;

COMMIT;

You use it like this

DECLARE @tvp dbo.OrderInfo;

INSERT @tvp (ProductID, Quantity)
VALUES (1, 5), (2, 4);

EXEC InsertOrder
    @OrderDate = '2022-01-01 09:02:32.232',
    @DeliveryAddress = 'Delivery Address: 1 Smith St, Melbourne',
    @ItemsTVP = @tvp,
    @OrderID = 123;

A real-life system would probably keep track of AvailableQuantity as well as ActualQuantity, and therefore you could see how much stock is held for live orders and whether you have oversold.