Hi and welcome to the forums. In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

In general you have a lot of issues with that stored proc. First of all is the name. It really should have a name that is descriptive of what it does. "store" does not give any indication what that stored proc is doing. Maybe something more like "AddItemToOrder"?

The next issue is using reserved words like Transaction as object names. This is not a good idea and makes your coding a lot more painful to work with. I would think that is something like a CustomerOrder. Then your detail table could be something like CustomerOrderDetail.

Be careful when using the money datatype. It is ok until you start doing math with that column, you will end up with rounding errors. This is because money is an approximate datatype. Numeric(9,2) would be my preference.

Next you really should look at using try/catch instead of named code blocks and goto statements.

All that aside I am not really sure what your actual question is here. If I am correct that you have header and detail tables I would split this into two stored procedures. The first one will create the Transaction row and with an output parameter you can retrieve the value of the identity. Then you will pass that as a parameter to the second stored proc that adds rows to the OrderProduct table.

For the record I am not a fan of storing a calculated price in the header. I would much prefer to just calculate it when it is needed. That eliminates all the hassle and pain of trying to keep it in synch.