自己写的一个数据库自定义函数实例
/******************************************************************
** 文件名:
** Copyright (c) 2003-2004 *********BESTEC
** 创建人:卢弋
** 日 期:2004-9-7
** 修改人:
** 日 期:
** 描 述:
** 根据输入的项目编号Proj_ID。
** 通过对外协报账（T_R_Invoice_MoneyApply）
** 差旅报销（T_Form_MoneyLend）
** 发票收取登记（T_Form_InvoiceIn）
** 的计算，得出 结题成本金额总额（SumBalanceCost）和 当前项目结题中的已结题过的结题成本金额（BalanceCost）与余额（DiffBalanceCost）
** 与其对应的 发票冲抵金额 （Invoice_Money）,
** 金额 （MoneyTotal）,
** 金额合计 （Total）
**
** 输入参数：@Proj_ID
** 版 本:v1.0
**----------------------------------------------------------------------------
******************************************************************/
CREATE FUNCTION getBalanceCost (@Proj_ID int)
RETURNS @tmp TABLE
(
SumBalanceCost money,
DiffBalanceCost money,
BalanceCost money,
Invoice_Money money,
MoneyWipeOut money,
Total money
)
AS
BEGIN
DECLARE @tempSumBalanceCost money
DECLARE @tempDiffBalanceCost money
DECLARE @tempBalanceCost money
DECLARE @tempInvoice_Money money
DECLARE @tempMoneyWipeOut money
DECLARE @tempTotal money
SELECT @tempInvoice_Money = isnull(Sum(Invoice_Money),0)
From T_R_Invoice_MoneyApply Where Cheque_ID in (Select distinct System_ID From T_Form_InnerChequeOut Where Proj_ID = @Proj_ID)
SELECT @tempMoneyWipeOut = isnull(Sum(MoneyWipeOut),0)
From T_Form_MoneyLend Where ApplyProjNumber in (Select ProjNumber From T_Form_ProjAccount Where Proj_ID = @Proj_ID)
SELECT @tempTotal = isnull(Sum(Total),0)
From T_Form_InvoiceIn
Where Proj_ID = @Proj_ID
--System_ID in (Select Invoice_ID From T_Form_DailyWipeOut Where InnerChecque_ID in (Select System_ID From T_Form_InnerChequeOut Where Proj_ID = @Proj_ID))
SELECT @tempBalanceCost = isnull(Sum(BalanceCost),0)
From T_Form_ProjBalance
Where Proj_ID = @Proj_ID
SELECT @tempSumBalanceCost = @tempInvoice_Money+@tempMoneyWipeOut+@tempTotal
SELECT @tempDiffBalanceCost = @tempSumBalanceCost-@tempBalanceCost
INSERT INTO @TMP (SumBalanceCost,DiffBalanceCost,BalanceCost,Invoice_Money,MoneyWipeOut,Total) Values (@tempSumBalanceCost,@tempDiffBalanceCost,@tempBalanceCost,@tempInvoice_Money,@tempMoneyWipeOut,@tempTotal)
RETURN
END
/*------------------代码结束---------------------*/
/* 分拆指定字符串 */
DECLARE
@Separator char(1), --分隔符
@ReturnDept varchar(1000) --返回值
SELECT @Separator=','
SELECT @ReturnDept=''
SELECT @Dept=@Dept+','
WHILE len(@Dept)>0
BEGIN
SELECT @dcode=substring(@Dept,0,CHARINDEX(@Separator,@Dept))
SELECT @ReturnDept=@ReturnDept+Char(39)+@dcode+Char(39)+@Separator
SELECT @Dept=substring(@Dept,CHARINDEX(@Separator,@Dept)+1,len(@Dept))
END
SELECT @ReturnDept=substring(@ReturnDept,0,len(@ReturnDept)-1)
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/******************************************************************
** 文件名:
** Copyright (c) 2003-2004 *********BESTEC
** 创建人:卢弋
** 日 期:2004-9-7
** 修改人:
** 日 期:
** 描 述:
** 输入参数：@Dept，@Year，@Month,@YearMonth
** 版 本:v1.0
**----------------------------------------------------------------------------
******************************************************************/
ALTER FUNCTION HRP_FactQuotaCompare (@Dept varchar(1000),@Year int,@Month int,@YearMonth int)
RETURNS @tmp TABLE
(
YearSpan int,
MonthSpan int,
Dept varchar(1000),
BasePayQuota money,
BasePayFact money,
BasePayBalance money,
BasePayBalanceTotal money,
PerformancePayQuota money,
PerformancePayFact money,
PerformancePayBalance money,
PerformancePayBalanceTotal money,
YearEndSubSidyQuota money,
YearEndSubSidyFact money,
YearFact money,
LaborFact money
)
AS
BEGIN
DECLARE @tempBasePayQuota money
DECLARE @tempBasePayFact money
DECLARE @tempBasePayBalance money
DECLARE @tempBasePayBalanceTotal money
DECLARE @tempPerformancePayQuota money
DECLARE @tempPerformancePayFact money
DECLARE @tempPerformancePayBalance money
DECLARE @tempPerformancePayBalanceTotal money
DECLARE @tempYearEndSubSidyQuota money
DECLARE @tempYearEndSubSidyFact money
DECLARE @tempYearFact money
DECLARE @tempLaborFact money
DECLARE @tempQuota money
DECLARE @tempFact money
DECLARE @dcode varchar(24)
DECLARE @Separator char(1) --分隔符
SELECT @Separator=','
SELECT @Dept=@Dept+','
WHILE len(@Dept)>0
BEGIN
SELECT @dcode=substring(@Dept,0,CHARINDEX(@Separator,@Dept))
SELECT @Dept=substring(@Dept,CHARINDEX(@Separator,@Dept)+1,len(@Dept))
SELECT @tempBasePayQuota = ISNULL(Sum(ISNULL(BasePayQuota,0)),0) From HR_PayQuotaSub
Where Dept = @dcode And PID In (Select SID From HR_PayQuota Where Status = '40' And YearMonth = @YearMonth)
--部门月度工资配额
SELECT @tempBasePayFact = ISNULL(Sum(ISNULL(BasePay,0)),0) From HR_FactPayInfoSub
Where PID In (Select SID From HR_FactPayInfo Where Dept = @dcode And Status = '40' And YearMonth = @YearMonth)
--部门月度工资实发
SELECT @tempBasePayBalance = @tempBasePayQuota - @tempBasePayFact
--部门月度工资节余
SELECT @tempFact = ISNULL(Sum(ISNULL(BasePay,0)),0) From HR_FactPayInfoSub
Where PID In (Select SID From HR_FactPayInfo Where Dept = @dcode And Status = '40' And YearMonth < (@YearMonth*10))
SELECT @tempQuota = ISNULL(Sum(ISNULL(BasePayQuota,0)),0) From HR_PayQuotaSub
Where Dept = @dcode And PID In (Select SID From HR_PayQuota Where Status = '40' And YearMonth < (@YearMonth*10))
SELECT @tempBasePayBalanceTotal = @tempQuota - @tempFact
--部门累计工资节余
--====================================================================================================================================
SELECT @tempPerformancePayQuota = ISNULL(Sum(ISNULL(PerformanceQuota,0)),0) From HR_PayQuotaSub
Where Dept = @dcode And PID In (Select SID From HR_PayQuota Where Status = '40' And YearMonth = @YearMonth)
--部门月度绩效配额
SELECT @tempPerformancePayFact = ISNULL(Sum(ISNULL(PerformancePay,0)),0) From HR_FactPayInfoSub
Where PID In (Select SID From HR_FactPayInfo Where Dept = @dcode And Status = '40' And YearMonth = @YearMonth)
--部门月度绩效实发
SELECT @tempPerformancePayBalance = @tempPerformancePayQuota - @tempPerformancePayFact
--部门月度绩效节余
SELECT @tempFact = ISNULL(Sum(ISNULL(PerformancePay,0)),0) From HR_FactPayInfoSub
Where PID In (Select SID From HR_FactPayInfo Where Dept = @dcode And Status = '40' And YearMonth < (@YearMonth*10))
SELECT @tempQuota = ISNULL(Sum(ISNULL(PerformanceQuota,0)),0) From HR_PayQuotaSub
Where Dept = @dcode And PID In (Select SID From HR_PayQuota Where Status = '40' And YearMonth < (@YearMonth*10))
SELECT @tempPerformancePayBalanceTotal = @tempQuota - @tempFact
--部门累计绩效节余
--====================================================================================================================================
SELECT @tempYearEndSubSidyQuota = ISNULL(Sum(ISNULL(YearEndBonusQuota,0)),0) From HR_PayQuotaSub
Where Dept = @dcode And PID In (Select SID From HR_PayQuota Where Status = '40' And YearMonth = @YearMonth)
--部门月度年终奖配额
SELECT @tempYearEndSubSidyFact = ISNULL(Sum(ISNULL(0,0)),0) From HR_FactPayInfoSub
Where PID In (Select SID From HR_FactPayInfo Where Dept = @dcode And Status = '40' And YearMonth = @YearMonth)
--部门月度年终奖实发$
--=====================================================================================================================================
SELECT @tempFact = ISNULL(Sum(ISNULL(BasePay,0)),0) From HR_FactPayInfoSub
Where PID In (Select SID From HR_FactPayInfo Where Dept = @dcode And Status = '40' And Convert(Int,SubString(Convert(varchar,YearMonth),0,4)) = @Year)
--部门年度工资实发
SELECT @tempYearFact = 0
SELECT @tempYearFact = @tempYearFact + @tempFact
SELECT @tempFact = ISNULL(Sum(ISNULL(PerformancePay,0)),0) From HR_FactPayInfoSub
Where PID In (Select SID From HR_FactPayInfo Where Dept = @dcode And Status = '40' And Convert(Int,SubString(Convert(varchar,YearMonth),0,4)) = @Year)
--部门年度绩效实发
SELECT @tempYearFact = @tempYearFact + @tempFact
SELECT @tempFact = ISNULL(Sum(ISNULL(0,0)),0) From HR_FactPayInfoSub
Where PID In (Select SID From HR_FactPayInfo Where Dept = @dcode And Status = '40' And Convert(Int,SubString(Convert(varchar,YearMonth),0,4)) = @Year)
--部门月度年终奖实发$
SELECT @tempYearFact = @tempYearFact + @tempFact
--====================================================================================================================================
SELECT @tempLaborFact = ISNULL(Sum(ISNULL(FactFee,0)),0) From HR_FactPayLaborSub
Where PID In (Select SID From HR_FactPayLabor Where Dept = @dcode And Status = '40' And YearMonth = @YearMonth)
--====================================================================================================================================
INSERT INTO @TMP (YearSpan,MonthSpan,Dept,BasePayQuota,BasePayFact,BasePayBalance,BasePayBalanceTotal,PerformancePayQuota,
PerformancePayFact,PerformancePayBalance,PerformancePayBalanceTotal,YearEndSubSidyQuota,YearEndSubSidyFact,YearFact,LaborFact)
Values (@Year,@Month,@dcode,@tempBasePayQuota,@tempBasePayFact,@tempBasePayQuota,@tempBasePayBalanceTotal,@tempPerformancePayQuota
,@tempPerformancePayFact,@tempPerformancePayBalance,@tempPerformancePayBalanceTotal,@tempYearEndSubSidyQuota
,@tempYearEndSubSidyFact,@tempYearFact,@tempLaborFact)
END
RETURN
END
/*------------------代码结束---------------------*/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
*/
/* 游标的使用 */
CREATE FUNCTION getManagerName(@Proj_ID int)
RETURNS Nvarchar(500)
BEGIN
DECLARE @ManagerName Nvarchar(500)
SET @ManagerName = ''
DECLARE @Name Nvarchar(50)
DECLARE manager_cursor CURSOR FOR
select ecode.ename from T_Form_ProjTeamSub left outer join ecode on T_Form_ProjTeamSub.Ecode = ecode.ecode
where T_Form_ProjTeamSub.Source_System_ID in (select System_ID from T_Form_ProjTeam where Proj_ID = @Proj_ID) and T_Form_ProjTeamSub.Title = '001'
OPEN manager_cursor
FETCH NEXT FROM manager_cursor
INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ManagerName = @ManagerName + @Name + ','
FETCH NEXT FROM manager_cursor
INTO @Name
END
CLOSE manager_cursor
DEALLOCATE manager_cursor
if(len(@ManagerName) > 1)
begin
if(substring(@ManagerName,len(@ManagerName),1) = ',')
return substring(@ManagerName,1,len(@ManagerName)-1)
end
RETURN @ManagerName
END
*/
