this my stored procedure and I'm sorry about names

ALTER proc [dbo].[storedP]

(@prdctName nvarchar(50)

,@ordrdQnty int

,@OrderID int

,@ordrPrice money

,@TrnDate datetime

,@TrnTotal money)

as

BEGIN TRY

begin transaction trs

DECLARE @TrnId int

SET @TrnId = 'SCOPE_IDENTITY'

INSERT INTO [dbo].[OrderProduct]

([OrdrId],

[prdctName]

,[ordrdQnty]

,[ordrPrice]

,[TrnId])

VALUES

(@OrderID,

@prdctName

,@ordrdQnty

,@ordrPrice

,@TrnId

)

INSERT INTO [dbo].[Transaction]

(

[OrdrId]

,[TrnDate]

,[TrnTotal])

VALUES

( @OrderId ,CURRENT_TIMESTAMP ,@TrnTotal)

commit tran

return 0

END TRY

BEGIN CATCH

rollback

return 1

END CATCH