I'm having a problem with an SQL stored procedure (shown below)
I'm taking in a dataset and adding the rows to table 'OrderItems'.
However, OrderItems refers to column 'Item' in table Items.
So, if an Item doesn't exist... i want to add it to Items first, get the ItemID and add it to OrderItems.
If it does exist, I want to get the ID and add it with it's existing ID?
Could anybody give me some pointers?
OrderItems has columns
OrderItemID, OrderID, ItemID, OrderItemQty
Items has columns
ItemID ... etc.
Thanks in advance to anybody who can lend a hand!
CREATE PROCEDURE [dbo].[CreateOrder]
/** Company **/
/** Make a unique identifier for order **/
declare @OrderID1 int
EXEC @OrderID1 = AddOrder @Company_ID, @Username
INSERT INTO OrderItems (OrderID,OrderItemQty)
@OrderID1 AS OrderID,
x.d.value('Diff','INT') AS OrderItemQty
FROM @Data.nodes('/NewDataSet/OrderItems') x(d)