No need to apologize about the names to me. The are painful but you are the one who has to suffer with them. 😉

I think the main issue is you don't understand how IDENTITY works. How are you getting the value for @OrderID to call this procedure? This is the new value for OrdrId in OrderProduct right? That means since you are using IDENTITY you don't know it yet.

The next issue is you have declared a variable @TrnId as an int and are trying set the value to a string literal. I am quite confused by TrnID. What is that for? It seems like that is maybe the primary key in Transaction. Why is there a column in the parent table for that?

Something like this might be closer but your structures seems to be a bit out of alignment too.

ALTER proc [dbo].[storedP]

(

@prdctName nvarchar(50)

,@ordrdQnty int

--,@OrderID int

,@ordrPrice money

,@TrnDate datetime

,@TrnTotal money

) as

BEGIN TRY

begin transaction trs

INSERT INTO [dbo].[OrderProduct]

(

--[OrdrId], You don't want to insert an explicit value here

[prdctName]

,[ordrdQnty]

,[ordrPrice]

--,[TrnId]

)

VALUES

(

--@OrderID,

@prdctName

,@ordrdQnty

,@ordrPrice

--,@TrnId

)

INSERT INTO [dbo].[Transaction]

(

[OrdrId]

,[TrnDate]

,[TrnTotal]

)

VALUES

(

SCOPE_IDENTITY() ,

CURRENT_TIMESTAMP ,

@TrnTotal

)

commit tran

return 0

END TRY

BEGIN CATCH

rollback

return 1

END CATCH

You should check BOL and read about the IDENTITY property so you understand what it does and how it works.

http://msdn.microsoft.com/en-us/library/ms186775.aspx