createsalaryreport.sql

财务管理系统 报账等基本功能实现 有完整的实现

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE  PROCEDURE CreateSalaryReport @@iM Int AS
-- 判断是否存在此临时表，如果存在则删除
IF EXISTS (SELECT * FROM dbo.sysobjects 
WHERE id= object_id(N'[dbo].[TmpTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[TmpTable]
DECLARE @sql Varchar(8000)
--设置查询语句
SET @sql = 'SELECT t.EmpId AS 员工编号,t.EmpName AS 姓名'
SET @sql=@sql+',dbo.GetStsSum(t.EmpId,-1,v.iMonth) AS 工资总额,
dbo.GetStsSum(t.EmpId,-2,v.iMonth) AS 应纳税金额,
dbo.GetStsSum(t.EmpId,-3,v.iMonth) AS 纳税后金额,
(dbo.GetStsSum(t.EmpId,-2,v.iMonth)-dbo.GetStsSum(t.EmpId,-3,v.iMonth)) AS 纳税金额,
dbo.GetStsSum(t.EmpId,-4,v.iMonth) AS 实发工资
 INTO TmpTable FROM'
--处理工资项目格式，统计指定月份的工资记录
SELECT @sql = @sql + '(SELECT EmpId,EmpName,SUM(CASE CAST(ItemId AS nVarchar(100)) WHEN 
  +CAST(ItemId AS nVarchar(100)) THEN Total ELSE 0 END) AS ItemName
  FROM v_Sts WHERE iMonth='+CAST(@@iM AS VARCHAR)+' Group by EmpId,EmpName) t,
  v_sts v WHERE v.iMonth='+CAST(@@iM AS VARCHAR)+'  
  GROUP BY t.EmpId,t.EmpName,v.iMonth'
--执行查询语句
EXEC(@sql)
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO