SQl SERVER STOR PROC.

ALTER proc [dbo].[balledger2ProjectWise]
@a as int,
@Sdate as  datetime,
@Edate as datetime,
@projectcode as int
as
begin
declare @date as datetime
declare @VTYPE as nvarchar(5)
DECLARE @VNO as numeric(18, 0)
DECLARE @DR as numeric(15, 2)
DECLARE @CR as numeric(15, 2)
DECLARE @lcode as numeric(18,2)
declare @Narr as nvarchar(50)
declare @Bal as numeric(15, 2)
declare @Balt as numeric(15, 2)
declare @ste as nvarchar(5)
set @Balt=0
set @Bal=0
DECLARE CURSOR1 cursor for
select Date,VTYPE ,VNO,DR,CR,LEDGERCODE,NARRATION from view_ledgopdetails where LEDGERCODE=@a and projectcode=@projectcode order by date
OPEN CURSOR1
FETCH NEXT FROM CURSOR1 INTO @date,@VTYPE,@VNO,@DR,@CR,@lcode,@Narr
WHILE @@FETCH_STATUS <> -1
BEGIN
set @Bal=@CR-@DR
set @Balt=@Bal+@Balt
if(@Balt>0)
set @ste='Cr'
else
set @ste='Dr'
insert into temballedger2(Date,VTYPE,VNO,DR,CR,Bal,ste,lcode,Narr)values(CAST(@date AS DATETIME),@VTYPE,@VNO,@DR,@CR,@Balt,@ste,@lcode,@Narr)

FETCH NEXT FROM CURSOR1 INTO @date,@VTYPE,@VNO,@DR,@CR,@lcode,@Narr
END
CLOSE CURSOR1
DEALLOCATE CURSOR1

DECLARE @Temptotal as numeric(15, 2)

select @Temptotal =(sum(CR)- sum(DR)) from temballedger2 where date < @Sdate group by lcode

if(@Temptotal>0)
set @ste='Cr'
else
set @ste='Dr'

select  @Sdate as Date, 'Op' as VTYPE  , null  as VNO, '' as NARRATION, sum(DR) as DR , sum(CR)as CR ,abs(sum(cr)-sum(dr)) AS BALANCE ,@ste as ste  from temballedger2 where date < @Sdate group by lcode
union
select Date, VTYPE , VNO ,Narr as NARRATION, DR , CR ,abs(Bal) AS BALANCE ,ste  from temballedger2 where date between @Sdate and @Edate and lcode=@lcode

truncate table temballedger2
end