Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
  • Why am I getting null values for lease_id in Rent?

    • 0
    • 0
    • 0
    • 1
    • 0
    • 0
    • 0
    • 639
    Answer it

    Passing primary key values in the parent table to foreign key values in child table with parent insert and child insert being in separate stored procedures.


    Parent is Lease table, the child is Rent table. I am getting null values for lease_id in Rent. Why?


    Parent's stored procedure

    ALTER procedure [dbo].[SPnew1]
    @tenant_id int, 
    @F_Name varchar(255),
    @L_Name  varchar(255),
    @Phone  varchar(255),
    @Email   varchar(255),
    @Age  int,
    @Gender varchar(255),

    @initial_date varchar(255),
    @end_date varchar(255),
    @deposit varchar(255)

    if @tenant_id=0
     insert into dbo.Tenant
    values( @F_Name,@L_Name,@Phone,@Email,@Age,@Gender )
    select @tenant_id=SCOPE_IDENTITY()

    insert into Lease (initial_date,end_date,deposit,tenant_id)values(@initial_date,@end_date,@deposit,@tenant_id);

    child's stored procedure

    ALTER procedure [dbo].[Rento]
    @rent_id int,
    @rent_fee varchar (255),
    @late_fee varchar (255),
    @due_date varchar (255),
    @service_charge varchar (255),
    @lease_id int,
    @pay_id int,
    @pay_date varchar (255),
    @pay_amount varchar (255),
    @receipt_no varchar (255)



    insert into Rent ( rent_fee,late_fee,due_date,service_charge,lease_id) values (@rent_fee,@late_fee,@due_date,@service_charge,@@identity)
    select @rent_id=SCOPE_IDENTITY();
    insert into Payment (pay_date,pay_amount,receipt_no,rent_id) values(@pay_date,@pay_amount,@receipt_no,@rent_id)

 1 Answer(s)

Sign In

Sign up using

Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: