procedure not returning desired result I am trying to generate a report from this procedure
create procedure [dbo].[hstatement]
(
    @fromdate date,
    @todate date,
    @prd varchar(8)
)
as 
begin
    create table #tempdata
    (
        LBrCode         int,
        prdCode         varchar(8),
        bal_date        datetime,
        bal_amt         numeric(18,2)
    )

    while @fromdate&lt;@todate
    begin
        insert into #tempdata
        select  LBrCode,
                substring(PrdAcctId,1,8) prdcd,
                convert(varchar(12),@fromdate,106) tdt,
                Balance4 
        from 
        (
            select LBrCode,
                   PrdAcctId,
                   CblDate dt,
                   Balance4,
                   rank() over (partition by LBrCode,PrdAcctId order by CblDate desc ) rnk 
            from cl_balance
            where rtrim(substring(PrdAcctId,1,8)) in (@prd)
                  and floor(cast(substring(PrdAcctId,17,8) as int))=0
                  and CblDate&lt;=@fromdate
        ) x 
        where rnk= 1 
              and LBrCode=1;
        
        set @fromdate=dateadd(day,1,@fromdate)
end;

The procedure is compiled successfully.
However, I am expecting a result as each date is between fromdate and todate, but I am getting only the first record.
In this section of your procedure:
where rtrim(substring(PrdAcctId,1,8)) in (@prd)
              and floor(cast(substring(PrdAcctId,17,8) as int))=0
              and CblDate&lt;=@fromdate

you are using the @fromdate as the filter and looking for the values earlier.  Then you increase the @fromdate value.  But the earliest date before Wednesday of the current week is still the same when I get the earliest date before Thursday of the current week - so wouldn't you end up with the same record?
I also assume there is more to the procedure as you only insert into the temp table and never query it?   What is the remainder of the procedure?  Please post the entire code.   Do you get multiple records with the same value as the output?