【SQL开源代码栏目提醒】:文章导读:在新的一年中,各位网友都进入紧张的学习或是工作阶段。网学会员整理了SQL开源代码-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