Insert data into 2 tables one-to-many relation

[Begood]

I want to write a Stored Procedure for these 2 tables ,
I have this SP but it doesn't achieve my point..

 
CREATE proc [dbo].[Sp_Insert]
@prdctName NVARCHAR(50),
@ordrdQnty INT,
@ordrPrice MONEY
--@TrnId INT
 
AS BEGIN TRAN
declare @TrnId int 
 
set @TrnId = SCOPE_IDENTITY()
--IDENT_CURRENT
 
Insert into [OrderProduct]
(prdctName ,ordrdQnty ,ordrPrice,TrnId )
values   (@prdctName,@ordrdQnty,@ordrPrice ,@TrnId)
 
 
declare @OrdrId int
declare @TrnTotal int
 
set @OrdrId=@@IDENTITY
--set @TrnTotal =  @ordrPrice
--@ordrdQnty *
 
if @@ERROR<>0 goto ERR_
 
 
Insert into [Transaction] ( OrdrId,TrnDate )--,TrnTotal
 
values (@OrdrId,GETDATE() )--,@TrnTotal)
if @@ERROR<>0 goto ERR_
 
commit tran 
return 0
 
ERR_:
 rollback tran
 return 1
 
GO
 
 

[Rabbit]

External storage sites are blocked by our firewalls. Attach the image to the thread itself.

You said the SP doesn't achieve your point. But you haven't told us what the "point" is so there's nothing we can help you with.

[Begood]

Thank you for your reply, I have 2 tables one of them is OrderProduct (orderId P.K.,ProdctName,Quantity,Price)which store the list of items that the customer want one by one , the second table I named it Transaction(TrnId P.K.,orderId f.K.,Datetime) which it suppose to group the list of Orders by one TrnId, So the realtion between these 2 tables is one(TrnId)-to-many(Orders).
In my SP I just store the orders without specifying TrnId ,
Actully I want to program the selling form.
I don't know what do you mean about the image!
I hope to be my point is clear now , and I'm sorry for my first bad description..

[Rabbit]

You still haven't explained what problem you're having. You said you want to store the order without transaction. Which doesn't make sense because now you have no way of tying the order to the transaction. But aside from that, you still need to explain what problem you're having implementing that. Is it giving you an error? What's the error code? Is it not working the way you want? What is it doing instead? How should it work? What is some sample data and results?

My comment about the image is that you used a site to store the image. My firewall blocks that site, so I can't see the image. Attach the image to the forum thread.

[Begood]

My SP store the orders in Transaction without specify the TrnId, there is no error, but I need to link orders with TrnId when It saved in Transaction table..

OrderedProduct

|Price|Quantity|ProductName|OrderId|
|10 | 2 | A | 1 |
| 80 | 1 | C | 2 |
| 30 | 5 | B | 3 |
| 60 | 3 | B | 4 |
========================================
Transaction

| Date | TrnNo |OrderId|
| 12:10/05-11-14 | 1 | 1 |
| 12:11/05-11-14 | 1 | 2 |
| 12:16/05-11-14 | 2 | 3 |
| 12:17/05-11-14 | 2 | 4 |

[Rabbit]

Is your question that you don't know how to get the OrderID that you just inserted?

[Begood]

my question is what is the required editing that SP need to store the Orders in Transaction table with one TrnId to let me then write query to calculate the total amount of the list of orders as group of TrnId.
Suppose there is customer who buy (1-a,2-b,3-c) as names of products ) these products should store to OrderProduct Table with the quantity and Price for each one products so I have 3 fields in OrderProduct then these Orders supposed to be stored in Transaction Table so I need to join these 3 orders with one TrnId as it one operation that happen at the same deal and for one customer ..
I hope I could making my idea clearer ..

[Rabbit]

If you only want one transaction record but link it to multiple order records, you need to store a transaction id in the order table. Not the other way around like you have now because that's impossible.

[Begood]

How I will get Transaction Id value If I stored it in the Order table, the Order table is filled with data at first ..

[Rabbit]

Insert the transaction first

[Begood]

Thank you ...........