alter procedure AutoCheckExpiry_ForPayment
@expiryDate date=null output,
@userId int =null output,
@packPeriod varchar(20)=null output,
@count int=null
as
begin
DECLARE @cmd AS NVARCHAR(max)
declare @payments table
(
    userId varchar(1000),
    PackagePeriod varchar(1000)
)

CREATE TABLE #result
        (
           PackagePeriod     varchar(20),
           UserId            INT,
           OldExpiryDate     DATE,
           AmountToPay       FLOAT,
           PyingAmount       FLOAT,
           Balance           FLOAT,
           LastPaidDate      DATE,
           Company_Id        INT
        )

create table #temp
        (
            PackagePeriod    varchar(20),
           UserId            INT,
           OldExpiryDate     DATE,
           AmountToPay       FLOAT,
           PyingAmount       FLOAT,
           Balance           FLOAT,
           LastPaidDate      DATE,
           Company_Id        INT
        )

        SET @cmd ='select Packages.PackagePeriod,Receive_Payment.UserId,
        Replace(CONVERT(VARCHAR(20), Receive_Payment.OldExpiryDate, 106),' ','-') AS OldExpiryDate,
        Receive_Payment.AmountToPay,Receive_Payment.PyingAmount,Receive_Payment.Balance,
        Receive_Payment.LastPaidDate,Receive_Payment.Company_Id
        from Receive_Payment
        join Packages on Receive_Payment.PackageId=Packages.PackageId
        join C_Register on Receive_Payment.UserId = C_Register.UserId
        where C_Register.LastPaidDate=Receive_Payment.LastPaidDate
        order by Receive_Payment.PayId DESC'
        Insert into #result
         EXEC(@cmd)

    insert into #temp(PackagePeriod)
    select PackagePeriod from #result where OldExpiryDate<=Replace(CONVERT(VARCHAR(20), (GetDate()-1), 106),' ','-')

    select @count=(select COUNT(PackagePeriod) from #temp)

    if(PackagePeriod='Monthly')
    begin
    update Receive_Payment
        set Receive_Payment.OldExpiryDate=(
        )

    values (select PackagePeriod from #result
        where OldExpiryDate<=Replace(CONVERT(VARCHAR(20), (GetDate()-1), 106),' ','-'))
    end
    else if(PackagePeriod='Quarterly')
    begin
    update Receive_Payment
        set Receive_Payment.OldExpiryDate=(
        )

    values (select PackagePeriod from #result
        where OldExpiryDate<=Replace(CONVERT(VARCHAR(20), (GetDate()-1), 106),' ','-'))
    end
        drop table #result
        drop table #temp
end