insert record in two table

create table Person

(

p_id int IDENTITY(1,1) not null primary key,

Fname varchar(50),

Lname varchar(50),

address varchar(50)

)

go



create table orders

(

o_id int IDENTITY(1,1) not null primary key,

oname varchar(50),

Qty int,

p_id int foreign key references Person(p_id),

prize Int

)

go



Create Proc Usp_Orders_Insert

(

@Param_Fname Varchar(50),

@Param_Lname Varchar(50),

@Param_Address Varchar(50),

@Param_oname Varchar(50),

@Param_Qty  Int,

@Param_prize Int

)

As

Begin

 Set Nocount On

 

 Declare @PersonID Int

 

 Begin Try

  Begin Tran 

   

  Insert Person(Fname, Lname, [address]) Values(@Param_Fname, @Param_Lname, @Param_Address)

  Select @PersonID = @@Identity 

 

  Insert orders(oname, Qty, p_id, prize) Values(@Param_oname, @Param_Qty, @PersonID, @Param_prize)

  

  Commit

   

 End Try

 Begin Catch

  Declare @Message Varchar(Max)

  Select @Message = 'Error : ' + ERROR_MESSAGE() + CHAR(10) + 'Line No: '+ Cast(ERROR_LINE() as Varchar(10))

  Raiserror(@Message,16,1)

  Rollback  

 End Catch

End

Go