how to insert single row in Order table and multiple rows in OrderDetails table via stored procedure.

Question

Thank you For Help

Let Me ExplainBetter .

I have four tables Named Customers_0, Orders_0, Order_Details and Products That Their Characteristics Are As Follows :

Customers_0

Column Name	Data Type	Allow Null
Cust_id	int	Unchecked
Name	nvarchar(30)	Unchecked
Family	nvarchar(30)	Unchecked
Phone	nvarchar(20)	Unchecked
Address	nvarchar(50)	Unchecked

Cust_id is PK and identity (1,1)

Orders_0

Column Name	Data Type	Allow Null
Ord_id	int	Unchecked
Ord_date	nchar(20)	Unchecked
Cust_id	int	Checked

Ord_id is PK and identity(1,1) ; Cust_id is FK .

There are 1-to-M Relationship BetweenCustomers_0 and Orders_0 .

Order_Details

Column Name	Data Type	Allow Null
Prod_id	int	Unchecked
Prod_name	nvarchar(50)	Unchecked
Quantity	numeric(18, 0)	Unchecked
Fee	money	Unchecked
Total_Amount	money	Unchecked
Unit	nchar(20)	Unchecked
Ord_id	int	Checked

Prod_id is PK and Not Identity ; Ord_id is FK .

There are 1-to-M Relationship BetweenOrders_0 and Order_Details .

I want insert single row in Order table and multiple rows in OrderDetails table via stored procedure.

Please hlpe me!

Please Give Me A Stored Procedure And A Algorithm Of This Sample.

Friday, June 21, 2013 1:48 PM

All replies

0

Sign in to vote

Step 1: insert into Orders table

Step 2: capture the identity value generated by the system using SCOPE_IDENTITY()

Step 3: insert into OrderDetails table using the results of the SCOPE_IDENTITY() value as FK

Geert Vanhove DCOD ------ http://geertvanhove.wordpress.com/ ----------- Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Friday, June 21, 2013 2:58 PM

Reply

|

Quote

|

0

Sign in to vote

Hello,

Just as Geert post above, you can use the SCOPE_IDENTITY() to get the last value for Ord_id in the Orders table and pass to the value into OrderDetails table.
Please refer to the sample Stored Procedure as Uri post in the following thread:
http://social.technet.microsoft.com/Forums/en-US/e5e78bf5-5420-4e58-bb7d-da466a5ba47f/how-to-insert-multiple-rows-in-multiple-tables-sql-2005

Regards,
Fanny Liu

Fanny Liu
TechNet Community Support

Saturday, June 22, 2013 5:52 AM

Reply

|

Quote

|

Moderator

USE tempdb;
go
--DROP TABLE dbo.OrderDetail;
--DROP TABLE dbo.Product;
--DROP TABLE dbo.OrderHeader;
--DROP TABLE dbo.Customer;
--DROP PROCEDURE dbo.InsertOrder;
--DROP TYPE dbo.OrderDetailInput;
GO
CREATE TABLE dbo.Customer
(	 Cust_id		BIGINT	PRIMARY KEY IDENTITY(-9223372036854775808, 1) NOT NULL 
	,Name			NVARCHAR(30)	UNIQUE NOT NULL
	,Family			NVARCHAR(30)	NULL
	,Phone			NVARCHAR(30)	NULL
	,Address		NVARCHAR(30)	NULL
);
CREATE TABLE dbo.OrderHeader
(	 Ord_id			BIGINT	PRIMARY KEY IDENTITY(-9223372036854775808, 1) NOT NULL 
	,Ord_date		DATE	NOT NULL
	,Cust_id		BIGINT	NOT NULL
	,CONSTRAINT FK_Customer FOREIGN KEY(Cust_id)
		REFERENCES dbo.Customer (Cust_id)
);
CREATE TABLE dbo.Product
(	 Prod_id		BIGINT	PRIMARY KEY IDENTITY(-9223372036854775808, 1) NOT NULL
	,Prod_Name		NVARCHAR(255)	UNIQUE NOT NULL
);
CREATE TABLE dbo.OrderDetail
(	 Ord_id			BIGINT		NOT NULL
	,Detail_id		BIGINT		PRIMARY KEY IDENTITY(-9223372036854775808, 1) NOT NULL 
	,Prod_ID		BIGINT
	,Quantity		INT
	,Fee			money
	,Total_Amount	as Quantity * Fee
	,CONSTRAINT FK_OrderHeader FOREIGN KEY(Ord_id)
		REFERENCES dbo.OrderHeader (Ord_id)
	,CONSTRAINT FK_Product FOREIGN KEY(Prod_ID)
		REFERENCES dbo.Product (Prod_ID)
);
CREATE TYPE dbo.OrderDetailInput as TABLE
(	 Prod_Name	NVARCHAR(255)	NOT NULL
	,Quantity	INT				NOT NULL
	,Fee		MONEY			NOT NULL
)

IF (OBJECT_ID('dbo.InsertOrder', 'P ') IS NULL)
BEGIN;   EXEC('CREATE PROCEDURE dbo.InsertOrder AS SELECT ''This should be replaced''')
END;
GO
ALTER PROCEDURE dbo.InsertOrder
	  @OrderDate	DATE
	 ,@Cust_Name	NVARCHAR(30)
	 ,@OrderDetail	dbo.OrderDetailInput readonly
AS
SET NOCOUNT ON;
DECLARE @ReturnCode	INT = 0;
DECLARE @OrderHeaderID BIGINT;
BEGIN TRY	

	INSERT INTO dbo.OrderHeader
	(	 Ord_date		
		,Cust_id		
	)
	SELECT	 @OrderDate
			,Cust_id
	FROM	dbo.Customer	Customer
	WHERE	Customer.Name = @Cust_Name;
	SELECT @OrderHeaderID = SCOPE_IDENTITY();

	INSERT INTO dbo.OrderDetail
	(	 Ord_id			
		,Prod_ID		
		,Quantity		
		,Fee	
	)
	SELECT	 @OrderHeaderID		
			,Product.Prod_id
			,OrderDetail.Quantity
			,OrderDetail.Fee
	FROM	@OrderDetail	OrderDetail	
	JOIN	dbo.Product		Product on OrderDetail.Prod_Name = Product.Prod_Name;
END TRY
BEGIN CATCH		IF (XACT_STATE()  <> 0) -- 1 OR -1 says there is an active transaction.
				BEGIN	ROLLBACK TRANSACTION;
				END;
				DECLARE @OOPSMessage	VARCHAR(MAX);
				SELECT	@OOPSMessage	=	'ERROR_PROCEDURE ('		+ ERROR_PROCEDURE()
										+	')' + CHAR(10) + ', ERROR_LINE ('		+ CAST(ERROR_LINE()		AS VARCHAR(25))
										+	')' + CHAR(10) + ', ERROR_NUMBER ('		+ CAST(ERROR_NUMBER()	AS VARCHAR(25))
										+	')' + CHAR(10) + ', ERROR_SEVERITY ('	+ CAST(ERROR_SEVERITY() AS VARCHAR(25))
										+	')' + CHAR(10) + ', ERROR_STATE ('		+ CAST(ERROR_STATE()	AS VARCHAR(25))
										+	')' + CHAR(10) + ', ERROR_MESSAGE ('	+ ERROR_MESSAGE()
										+	')'; 		
				RAISERROR (@OOPSMessage, 11, 10) WITH LOG;	
				SELECT @ReturnCode = -1; 
END CATCH;
RETURN @ReturnCode;
GO


INSERT INTO dbo.Customer
(	 Name			
	,Family			
	,Phone			
	,Address		
) VALUES
 (	N'Sam Adams', N'Adams Family', N'Adams Phone', N'Adams Address')
,(	N'Sam Smith', N'Smith Family', N'Smith Phone', N'Smith Address');
SELECT *
FROM dbo.Customer;
INSERT INTO dbo.Product
(	 Prod_Name		
) VALUES
 (N'Big Apple')
,(N'Small Apple')
,(N'Strawberry');
SELECT *
FROM dbo.Product;

DECLARE @NewOrderDetail dbo.OrderDetailInput; 

Insert @NewOrderDetail
(	 Prod_Name	
	,Quantity	
	,Fee		
)	values
 (N'Big Apple', 1, $1.23)
,(N'Small Apple', 10, $10.23)
,(N'Strawberry', 25, $22.23);

EXEC dbo.InsertOrder
	'2013-06-21'
	,N'Sam Adams'
	,@NewOrderDetail 

SELECT *
FROM dbo.OrderHeader

SELECT *
FROM  dbo.OrderDetail

This should be close to what you need.

Tom G.

Saturday, June 22, 2013 3:58 PM

0

Sign in to vote

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you failed). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

This is minimal polite behavior on SQL forums. What you did post is useless and needs to be corrected. For example, no competent data modeler would use INTEGER for a customer_id, MONEY is proprietary and does not do correct math (Google it!), you do knwo the length of a phone number (E.123 Standard)

I have four tables Named Customers, Orders, Order_Details and Products That Their Characteristics Are As Follows :

CREATE TABLE Customers
(cust_id CHAR(16) NOT NULL PRIMARY KEY, -- size of credit card #
cust_first_name VARCHAR(20) NOT NULL,
cust_last_name VARCHAR(20) NOT NULL,
cust_phone NOT NULL CHAR(18) NOT NULL,
san CHAR(10) NOT NULL);

An address of VARCHAR(50) is wrong; each line ion a USPS address is VARCHAR(35) and there are five of them. However, serious databases have a SAN (Standard Address Number). This gives you the full streeet address.

>> cust_id is PK and IDENTITY (1,1) <<

You have never read a book or had a course on RDBMS; why do you think the count of physical insertion attempts to one file on one disk drive is an attribute of customer?? How do you verify and validate this magical Kabbalah number?

CREATE TABLE Orders
(order_nbr CHAR(10) NOT NULL PRIMARY KEY,
order_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
cust_id CHAR(10) NOT NULL
REFERENCES Customers(cust_id)
ON DELETE CASCADE
ON UPDATE CASCADE);

Again, no competent SQL programmer would use IDENTITY(1,1). The use of strings for a date is dangerous, insanely slow and stupid.

CREATE TABLE Order_Details
(order_nbr CHAR(10) NOT NULL
REFERENCES Orders(order_nbr)
ON DELETE CASCADE
ON UPDATE CASCADE,
product_gtin CHAR(15) NOT NULL
REFERENCES Inventory (product_gtin),
order_qty INTEGER NOT NULL);

Products are identified by some industry standard. The GTIN is the most general, so I will use it. The product name should not be here; you have the GTIN and you find the product name in Inventory. You will get the unit price, product name, and other data from the3 Inventory; it will not be in the order details. Likewise, we do not keep redundant data like a total amount. The term “fee” makes no sense in this context. We need the quantity and unit price for an order item.

Do you know about normalization? You need to learn.

>> please give me a stored procedure and an algorithm <<

This smells like a homework assignment. We will need to check and report you for cheating, if so. The worst way would be to use SCOPE_IDENTITY() and keep the non-RDBMS disaster you have in your non_DDL ASCII pictures.

Here is a skeleton that you can research. It uses the long parameter list SQL idiom. You will probably need to research it, since you did not know normalization.

CREATE PROCEDURE Place_Order
(@in_cust_id CHAR(16),
@in_order_nbr CHAR(10),
@in_order_date = CURRENT_TIMESTAMP,
@in_product_gtin_1 = NULL,
@in_order_qty_1 = NULL,
…
@in_product_gtin_n = NULL,
@in_order_qty_n = NULL)

BEGIN
INSERT INTO Orders
VALUES (@in_order_nbr, @in_order_date, @in_cust_id);

INSERT INTO Order_Details
SELECT @in_order_nbr, X.product_gtin, X.order_qty
FROM (VALUES (@in_product_gtin_1, @in_order_qty_1),
(@in_product_gtin_2, @in_order_qty_2),
...
(@in_product_gtin_n, @in_order_qty_n)) AS X
WHERE X.product_gtin IS NOT NULL;
END;

You will need to add error handling, validations and other things to make it a real proc.
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Saturday, June 22, 2013 5:56 PM

Reply

|

Quote

|

0

Sign in to vote

thank you for help dear Tom G.

how i can call and execute this table_valued stored procedure in vb.net 2010

USE tempdb;
go
--DROP TABLE dbo.OrderDetail;
--DROP TABLE dbo.Product;
--DROP TABLE dbo.OrderHeader;
--DROP TABLE dbo.Customer;
--DROP PROCEDURE dbo.InsertOrder;
--DROP TYPE dbo.OrderDetailInput;
GO
CREATE TABLE dbo.Customer
( Cust_id BIGINT PRIMARY KEY IDENTITY(-9223372036854775808, 1) NOT NULL
,Name NVARCHAR(30) UNIQUE NOT NULL
,Family NVARCHAR(30) NULL
,Phone NVARCHAR(30) NULL
,Address NVARCHAR(30) NULL
);
CREATE TABLE dbo.OrderHeader
( Ord_id BIGINT PRIMARY KEY IDENTITY(-9223372036854775808, 1) NOT NULL
,Ord_date DATE NOT NULL
,Cust_id BIGINT NOT NULL
,CONSTRAINT FK_Customer FOREIGN KEY(Cust_id)
REFERENCES dbo.Customer (Cust_id)
);
CREATE TABLE dbo.Product
( Prod_id BIGINT PRIMARY KEY IDENTITY(-9223372036854775808, 1) NOT NULL
,Prod_Name NVARCHAR(255) UNIQUE NOT NULL
);
CREATE TABLE dbo.OrderDetail
( Ord_id BIGINT NOT NULL
,Detail_id BIGINT PRIMARY KEY IDENTITY(-9223372036854775808, 1) NOT NULL
,Prod_ID BIGINT
,Quantity INT
,Fee money
,Total_Amount as Quantity * Fee
,CONSTRAINT FK_OrderHeader FOREIGN KEY(Ord_id)
REFERENCES dbo.OrderHeader (Ord_id)
,CONSTRAINT FK_Product FOREIGN KEY(Prod_ID)
REFERENCES dbo.Product (Prod_ID)
);
CREATE TYPE dbo.OrderDetailInput as TABLE
( Prod_Name NVARCHAR(255) NOT NULL
,Quantity INT NOT NULL
,Fee MONEY NOT NULL
)

IF (OBJECT_ID('dbo.InsertOrder', 'P ') IS NULL)
BEGIN; EXEC('CREATE PROCEDURE dbo.InsertOrder AS SELECT ''This should be replaced''')
END;
GO
ALTER PROCEDURE dbo.InsertOrder
@OrderDate DATE
,@Cust_Name NVARCHAR(30)
,@OrderDetail dbo.OrderDetailInput readonly
AS
SET NOCOUNT ON;
DECLARE @ReturnCode INT = 0;
DECLARE @OrderHeaderID BIGINT;
BEGIN TRY

INSERT INTO dbo.OrderHeader
( Ord_date
,Cust_id
)
SELECT @OrderDate
,Cust_id
FROM dbo.Customer Customer
WHERE Customer.Name = @Cust_Name;
SELECT @OrderHeaderID = SCOPE_IDENTITY();

INSERT INTO dbo.OrderDetail
( Ord_id
,Prod_ID
,Quantity
,Fee
)
SELECT @OrderHeaderID
,Product.Prod_id
,OrderDetail.Quantity
,OrderDetail.Fee
FROM @OrderDetail OrderDetail
JOIN dbo.Product Product on OrderDetail.Prod_Name = Product.Prod_Name;
END TRY
BEGIN CATCH IF (XACT_STATE() <> 0) -- 1 OR -1 says there is an active transaction.
BEGIN ROLLBACK TRANSACTION;
END;
DECLARE @OOPSMessage VARCHAR(MAX);
SELECT @OOPSMessage = 'ERROR_PROCEDURE (' + ERROR_PROCEDURE()
+ ')' + CHAR(10) + ', ERROR_LINE (' + CAST(ERROR_LINE() AS VARCHAR(25))
+ ')' + CHAR(10) + ', ERROR_NUMBER (' + CAST(ERROR_NUMBER() AS VARCHAR(25))
+ ')' + CHAR(10) + ', ERROR_SEVERITY (' + CAST(ERROR_SEVERITY() AS VARCHAR(25))
+ ')' + CHAR(10) + ', ERROR_STATE (' + CAST(ERROR_STATE() AS VARCHAR(25))
+ ')' + CHAR(10) + ', ERROR_MESSAGE (' + ERROR_MESSAGE()
+ ')';
RAISERROR (@OOPSMessage, 11, 10) WITH LOG;
SELECT @ReturnCode = -1;
END CATCH;
RETURN @ReturnCode;
GO

INSERT INTO dbo.Customer
( Name
,Family
,Phone
,Address
) VALUES
( N'Sam Adams', N'Adams Family', N'Adams Phone', N'Adams Address')
,( N'Sam Smith', N'Smith Family', N'Smith Phone', N'Smith Address');
SELECT *
FROM dbo.Customer;
INSERT INTO dbo.Product
( Prod_Name
) VALUES
(N'Big Apple')
,(N'Small Apple')
,(N'Strawberry');
SELECT *
FROM dbo.Product;

DECLARE @NewOrderDetail dbo.OrderDetailInput;

Insert @NewOrderDetail
( Prod_Name
,Quantity
,Fee
) values
(N'Big Apple', 1, $1.23)
,(N'Small Apple', 10, $10.23)
,(N'Strawberry', 25, $22.23);

EXEC dbo.InsertOrder
'2013-06-21'
,N'Sam Adams'
,@NewOrderDetail

SELECT *
FROM dbo.OrderHeader

SELECT *
FROM dbo.OrderDetail

Wednesday, June 26, 2013 9:29 AM

Reply

|

Quote

|

0

Sign in to vote

I don't code VB.NET. I do it with C#. BING VB.NET table valued parameters and you will find examples.
Tom G.

Wednesday, June 26, 2013 12:35 PM

Reply

|

Quote

|

0

Sign in to vote

thanks for help dear Tom G.

Can you give me an Algorithm(code) for this stored procedure in c#.

Monday, August 05, 2013 8:46 AM

Reply

|

Quote

|

Asked by:

how to insert single row in Order table and multiple rows in OrderDetails table via stored procedure.

Question

All replies