本月累计已锁定付款金额

本月累计已锁定付款金额proce与调用

function pl5()
{
var s1=txtcompanyName.value;
var sqlInsertChange1 = "exec Pro_SelectMoneyInfo_New2 '"+s1+"'";
dataset6.Open(sqlInsertChange1);
text14.value=dataset6.Fields.Field['totalje'].Value;
}
 

USE [C6]
GO
/****** 对象:  StoredProcedure [dbo].[Pro_SelectMoneyInfo_New2]    脚本日期: 05/21/2012 12:09:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter proc [dbo].[Pro_SelectMoneyInfo_New2] 
@comp varchar(1000)

as

declare @sql varchar(8000)

begin
if exists(

--declare @comp varchar(1000)
select id, companyName, (JinEHeJi) as totalje
from Form_YS_PayApply b 
inner join JHOA_Approve c
on b.ID=c.AppO_Values 
--and c.App_Over=1  and c.App_IdeaFlag=9 
and c.Del_flag=0
Group by id,companyName,JinEHeJi,year(c.App_EndTime),month(c.App_EndTime)
having sum(JinEHeJi)>=0 and year(getdate())=year(c.App_EndTime) 
and  companyName=''+@comp+'' 
and month(getdate())=month(c.App_EndTime)
)

begin 
create table #TempTable2
( id varchar(50), companyName char(100),JinEHeJi  money)

insert into #Temptable2
select  id, companyName,(JinEHeJi) as totalje
from Form_YS_PayApply b 
inner join JHOA_Approve c
on b.ID=c.AppO_Values 
--and c.App_Over=1  and c.App_IdeaFlag=9 
and c.Del_flag=0
Group by id,companyName,JinEHeJi,year(c.App_EndTime),month(c.App_EndTime)
having sum(JinEHeJi)>=0 and year(getdate())=year(c.App_EndTime) 
and  companyName=''+@comp+'' 
and month(getdate())=month(c.App_EndTime)

set @sql ='select sum(JinEHeJi) as totalje from #TempTable2  '
exec(@sql)
delete from #TempTable2
end

else
begin
	select 0 as totalje
end
end
/*
select * from  Form_YS_PayApply b 
inner join JHOA_Approve c
on b.ID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
and year(getdate())=year(c.App_EndTime) 
and month(getdate())=month(c.App_EndTime)
and companyName = '江苏大明金属制品有限公司'
*/


--exec Pro_SelectMoneyInfo_New2 '江苏大明金属制品有限公司'