Please help me out

[TanmayQuery]

I have written a sql procedure, i want to pass delared variable @cnt in line131,char24
below is the PL Code:
CREATE PROCEDURE PL
@dt varchar(10),
@stock as bit --1/true for updated and 0/false for previous executed data
AS

set dateformat dmy
set nocount on

Declare @sdate VARCHAR(10)
Declare @clup as Decimal(18,2)
declare @cnt as integer
DECLARE @amt as decimal(18,2),@ gamt as decimal(18,2), @damt as decimal(18,2), @camt as decimal(18,2)

delete from temp_pl
delete from temp_bal

Select @sdate = Convert(varchar (10),StartDate, 103 ) From Param
exec show_trial @dt
-- Now data is in temp_trial
if( @Stock =1 )
begin
exec Closing @sdate, @dt
end

select party_nm as acc, dr_amt as Amtdr, cr_amt as Amtcr into #Tlist from temp_trial WHERE party_cd not in (150,152,151,38 ,36,33,32,35,34 )
--select * from #tlist

SELECT Acc,AmtDr,AmtCr ,Type, odr into #tmpop FROM (
Select 'Opening Stock (UP)' as Acc ,'AmtDr'= case when oup >0 then oup else 0 end ,
'AmtCr'= case when oup <0 then abs(oup) else 0 end , 'T' as Type ,1 as odr From temp_stock_bala nce
union
Select 'Opening Stock (Ex UP)' as Acc ,'AmtDr'= case when oex >0 then oex else 0 end ,
'AmtCr'= case when oex <0 then abs(oex) else 0 end , 'T' as Type ,1 as odr From temp_stock_bala nce

union
Select 'Purchase (UP)' as Acc ,'AmtDr'= case when pup >0 then pup else 0 end ,
'AmtCr'= case when pup <0 then abs(pup) else 0 end , 'T' as Type ,2 as odr From temp_stock_bala nce
union
Select 'Purchase (Ex UP)' as Acc ,'AmtDr'= case when pex >0 then pex else 0 end ,
'AmtCr'= case when pex <0 then abs(pex) else 0 end , 'T' as Type ,2 as odr From temp_stock_bala nce
union
Select 'Sales (UP)' as Acc , 'AmtDr' =case when sUp < 0 then abs(sup) else 0 END ,
'AmtCr' =case when sup > 0 then sup else 0 eND, 'T' as Type ,2 as odr From temp_stock_bala nce
union
Select 'Sales (Ex-UP)' as Acc , 'AmtDr' =case when sex < 0 then abs(sex) else 0 END ,
'AmtCr' =case when sex > 0 then sex else 0 eND, 'T' as Type ,2 as odr From temp_stock_bala nce

union
Select 'Closing Stock(UP)' as Acc , 'AmtDr' =case when CUp < 0 then abs(cup) else 0 END ,
'AmtCr' =case when Cup > 0 then cup else 0 eND, 'T' as Type ,3 as odr From temp_stock_bala nce
union
Select 'Closing Stock(Ex-UP)' as Acc , 'AmtDr' =case when Cex < 0 then abs(cex) else 0 END ,
'AmtCr' =case when Cex > 0 then cex else 0 eND, 'T' as Type ,3 as odr From temp_stock_bala nce
)A

Select * into #tmpl from(
SELECT Acc, AmtDr, AmtCr ,Type ,odr
From (Select * from #tlist ) a INNER JOIN (Select party_nm,Ac_typ e as Type,odr From Party where Ac_type IN ('P','T') )
Acmast ON a.Acc = Acmast.Party_nm
UNION
select Acc,AmtDr,AmtCr ,Type , odr from #tmpop
) a

update #tmpl set Amtdr = abs(AmtCr) where AmtCr < 0
update #tmpl set AmtCr =abs( Amtdr) where AmtDr < 0
/*
--Gp
set @clup = (Select 'GP' = case
when sum(AmtDr)>= sum(AmtCr) then (Sum(AmtDr) -Sum(AmtCr) )* -1

When sum(AmtDr) < Sum(AmtCr) then (Sum(AmtCr) -Sum(AmtDr) ) end From #tmpl where Type= 'T' )
*/
--TRADING A/C
SELECT Acc , AmtCr,type into #CT FROM #tmpL WHERE (AmtCr > 0 and Type ='T') order by odr Asc, Acc asc
SELECT Acc , AmtDr,type into #DT FROM #tmpL WHERE (AmtDr > 0 and Type ='T') order by odr Asc, Acc asc

SELECT IDENTITY(int, 1,1) AS ids,Acc , AmtCr,type into #CreditT FROM #CT
SELECT IDENTITY(int, 1,1) AS ids,Acc , AmtDr,type into #debitT FROM #DT

Select IDENTITY(int, 1,1) AS sr_no, * INTO #TRADE1 from
( Select d.Acc as Accdr , d.AmtDr as AmtDr , C.acc as AccCr ,C.amtCr as AmtCr ,'T' as Type from
#debitT d Full outer join #creditT c on c.ids =d.ids
)A
insert into #trade1 values(null,0,n ull,0,'T') --Line before sum

select @amt=sum(isnull (amtcr,0))-sum(isnull(amtd r,0)) from #trade1

select @damt=sum(isnul l(amtdr,0)) from #trade1
select @camt=sum(isnul l(amtcr,0)) from #trade1
insert into #trade1 values (null,@damt,nul l,@camt,'T')
if @amt>0
begin
select @gamt=sum(isnul l(amtcr,0)) from #trade1
insert into #trade1 (accdr,amtdr,ty pe) values('Gross Profit C/F to P&L',@amt,'T' )
end
else if @amt<0
begin
select @gamt=sum(isnul l(amtdr,0)) from #trade1
insert into #trade1 (acccr,amtcr,ty pe) values('Gross Loss C/F to P&L',abs(@amt), 'T')
end
else
begin
select @gamt=sum(isnul l(amtdr,0)) from #trade1
end
insert into #trade1 values(null,0,n ull,0,'T') --Line after gross
insert into #trade1 values(null,@ga mt,null,@gamt,' T')
insert into #trade1 values(null,0,n ull,0,'T') --Line after Trade gross
if @amt>0
begin
insert into #trade1 (acccr,amtcr,ty pe) values('Gross Profit B/F from P&L',@amt,'P' )
end
else if @amt<0
begin
insert into #trade1 (accdr,amtdr,ty pe) values('Gross Loss B/F from P&L',abs(@amt), 'P')
end
else
begin
insert into #trade1 values(null,nul l,null,null,'T' )
end
Select * INTO #TRADE from #trade1 order by sr_no
----------------------------------------------------------------------------------------------------
--P/L A/C

SELECT Acc , AmtCr,type into #CP FROM #tmpL WHERE (AmtCr > 0 and Type ='P') order by odr Asc, Acc asc
SELECT Acc , AmtDr,type into #DP FROM #tmpL WHERE (AmtDr > 0 and Type ='P') order by odr Asc, Acc asc

SELECT IDENTITY(int, 1,1) AS ids,Acc , AmtCr,type into #CreditP FROM #CP
SELECT IDENTITY(int, 1,1) AS ids,Acc , AmtDr,type into #debitP FROM #DP

select @cnt=count(SR_N O)+1 from #trade

select IDENTITY (int,@cnt,1) AS sr_no,accdr,amt dr,acccr,amtcr, type INTO #PL_TEMP1 from #trade t WHERE t.type='P'

insert into #pl_temp1 (accdr,amtdr,ac ccr,amtcr,type)
Select d.Acc ,d.AmtDr, C.acc ,C.amtCr as AmtCr ,'P' as Type from
#debitP d Full outer join #creditP c on c.ids =d.ids

delete from #trade WHERE type='P'
select @amt=sum(isnull (amtcr,0))-sum(isnull(amtd r,0)) from #PL_TEMP1

insert into #PL_TEMP1 values(null,0,n ull,0,'P') --line before sum

select @amt=sum(isnull (amtcr,0))-sum(isnull(amtd r,0)) from #PL_TEMP1
insert into temp_bal values(@amt)

select @damt=sum(isnul l(amtdr,0)) from #PL_TEMP1
select @camt=sum(isnul l(amtcr,0)) from #PL_TEMP1
insert into #PL_TEMP1 values (null,@damt,nul l,@camt,'P')
if @amt>0
begin
select @gamt=sum(isnul l(amtcr,0)) from #PL_TEMP1
insert into #PL_TEMP1 (accdr,amtdr,ty pe) values('Net Profit C/F to Balance Sheet',@amt,'P' )
end
else if @amt<0
begin
select @gamt=sum(isnul l(amtdr,0)) from #PL_TEMP1
insert into #PL_TEMP1 (acccr,amtcr,ty pe) values('Net Loss C/F to Balance Sheet',abs(@amt ),'P')
end
else
begin
select @gamt=sum(isnul l(amtdr,0)) from #PL_TEMP1
end
insert into #PL_TEMP1 values(null,0,n ull,0,'P') --Line after NET
insert into #PL_TEMP1 values(null,@ga mt,null,@gamt,' P')
insert into #PL_TEMP1 values(null,0,n ull,0,'P') --Line after PL NET

Select * INTO #PL_TEMP from #PL_TEMP1 order by sr_no

INSERT INTO temp_PL
SELECT * FROM
( select * from #PL_TEMP
UNION
SELECT * FROM #TRADE
) a order by sr_no
GO




please help me

[hariharanmca]

Do not post your procedure. Just explain your problem in detail and post code in the pirticular error area.