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
    • 667
    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)

    as
    if @tenant_id=0
    begin 
     insert into dbo.Tenant
    (F_Name,L_Name,Phone,Email,Age,Gender)  
    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);
    end

    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)

    as

    begin

    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)
    end

 1 Answer(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
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: