March 9, 2018 at 8:19 am
Hello, This is a novice question, but looking to learn how to do this correctly. What I am looking to do is process an Order into an CustomerOrder Table then place the Customer Order Details into another table based on varying selections made via a web page.
Referencing the sample code below, lets say a Customer selected the Products Apple and Strawberries and this completed the order.
I would want the database to determine:
1. Products selected via the web page
2. Create a record in the Customer Order table
3. Insert the OrderID and ProductID into the Order Details table
I am unsure of the best way to do this, do I want to:
1. Create a CURSOR to create an OrderID in the CustOrder table
2. Then pass the OrderID via CURSOR to the OrderDetail with the selected ProductIDs? If yes, would I use an IN CLAUSE to determine the Products selected?
I have provided sample code below. Could someone provide a recommended solution?
-- DROP TABLE #Products
CREATE TABLE #Products (PID int, ProductName varchar(25))
INSERT INTO #Products (PID, ProductName) VALUES (1,'Apple')
INSERT INTO #Products (PID, ProductName) VALUES (2,'Orange')
INSERT INTO #Products (PID, ProductName) VALUES (3,'Grapes')
INSERT INTO #Products (PID, ProductName) VALUES (4,'Pear')
INSERT INTO #Products (PID, ProductName) VALUES (5,'Strawberries')
INSERT INTO #Products (PID, ProductName) VALUES (6,'Avocado')
-- 1. Create Customer Order Record
-- DROP TABLE #CustOrder
DECLARE @CustNum int
SET @CustNum = 999999
CREATE TABLE #CustOrder (OrderID int IDENTITY (1,1), CustNum int)
INSERT INTO #CustOrder (CustNum) VALUES (@CustNum)
-- DROP TABLE #OrderDetail
CREATE TABLE #CustOrderDetail(CODID int IDENTITY (1,1), OrderID int, PID int)
INSERT INTO #CutOrderDetail (OrderID, PID)
March 9, 2018 at 10:16 am
That seems like that would be better done at the application layer.
March 9, 2018 at 11:29 am
ZZartin - Friday, March 9, 2018 10:16 AMThat seems like that would be better done at the application layer.
Regardless of where the logic takes place, the need will still exist to do either both inserts or none. Because of that, you typically encase them in an explicit transaction, so whether you use a stored procedure or direct SQL statements once you start interacting with the database, the typical sequence of code is:
CREATE PROCEDURE Insert (
@CustID int,
@ProductList varchar(8000)
)
SET NOCOUNT ON:;
BEGIN TRY
BEGIN TRAN
INSERT INTO Customer (CustID)
VALUES (@CustID);
INSERT INTO Orders (CustID, ProductID)
SELECT @CustID, CONVERT(int, Item)
FROM dbo.DelimitedSplit8K(@ProductList, ',');
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
RAISERROR('Error inserting data', 16, 1);
END CATCH
GO
Note that using a comma-delimited string and the String Splitter function mentioned make this kind of thing a piece of cake. The web side still has to trap the error if it occurs, but I'll assume the poster already knows how to deal with that, as the above sequence is NOT going to keep the data if an error occurs.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 12, 2018 at 4:12 pm
Thank you kindly for your help!
The one other thing missing that I am trying to accomplish would be to get the generated IDENTITY PK from the Customer Table and pass it into the Orders Table. Is there a way to do this? What I am hoping to do is use the Order table to contain high level information and the Details table to contain the Order information and be able to reference the Order Table ID to be able to query details.
March 13, 2018 at 1:56 am
rjjh78 - Monday, March 12, 2018 4:12 PMThank you kindly for your help!The one other thing missing that I am trying to accomplish would be to get the generated IDENTITY PK from the Customer Table and pass it into the Orders Table. Is there a way to do this? What I am hoping to do is use the Order table to contain high level information and the Details table to contain the Order information and be able to reference the Order Table ID to be able to query details.
For individual inserts, take a look at SCOPE_IDENITY
For multiple inserts, take a look at the OUTPUT clause, specifically as part of the INSERT statement.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply