＋设为首页 ＋添加到收藏夹
CREATE PROCEDURE Get_TC_SUM
@TimeTerm nchar(100)=
AS
declare @strSQL nchar(4000)
set @strSQL=
select employee.EM_NO,employee.name,job.job_name,
isnull(CC.TC_SUM_SV,0) as TC_SUM_SV,isnull(CC.DO_SUM_SV,0) as DO_SUM_SV,
isnull(CC.TC_SUM_DG,0) as TC_SUM_DG,isnull(CC.DO_SUM_DG,0) as DO_SUM_DG,
isnull(TC_JS,0) as TC_JS,
TC_SUM=isnull((TC_SUM_SV+TC_SUM_DG+TC_JS),0) from
(select AA.*, BB.JS_money as TC_JS from (
select EM_ID,
TC_SUM_SV=sum(case when unit=分钟 or unit=次 then TC_Money else 0 end),
DO_SUM_SV=sum(case when unit=分钟 or unit=次 then DO_Money else 0 end),
TC_SUM_DG=sum(case when unit<>分钟 and unit<>次 then TC_Money else 0 end),
DO_SUM_DG=sum(case when unit<>分钟 and unit<>次 then DO_Money else 0 end)
from TC_Do_All
where @timeterm －－这里一个
group by EM_ID) as AA
left outer join (
select JS_EM_ID,JS_Money=sum(JS_Money)
from dan
where @timeterm －－这里一个
group by JS_EM_ID) as BB
on AA.EM_ID=BB.JS_EM_ID) as CC right outer join employee on employee.EM_ID=CC.EM_ID left outer join job on employee.job_id=job.job_id order by employee.EM_NO
--print @strSQL
if @strSQL !=
exec sp_executesql @strSQL
,N
@TimeTerm nchar(100)
,@TimeTerm
我是这样传递@timeterm参数的：
Get_TC_SUM @TimeTerm= DATEDIFF(dd, pay_T,2005-9-8 )=0
提示如下的错误：
服务器: 消息 156，级别 15，状态 1，行 16
在关键字 group 附近有语法错误。
服务器: 消息 156，级别 15，状态 1，行 21
在关键字 group 附近有语法错误。
按道理，它难道不会得出下面的搜索语句的吗？：
select employee.EM_NO,employee.name,job.job_name,
isnull(CC.TC_SUM_SV,0) as TC_SUM_SV,isnull(CC.DO_SUM_SV,0) as DO_SUM_SV,
isnull(CC.TC_SUM_DG,0) as TC_SUM_DG,isnull(CC.DO_SUM_DG,0) as DO_SUM_DG,
isnull(TC_JS,0) as TC_JS,
TC_SUM=isnull((TC_SUM_SV+TC_SUM_DG+TC_JS),0) from
(select AA.*, BB.JS_money as TC_JS from (
select EM_ID,
TC_SUM_SV=sum(case when unit=分钟 or unit=次 then TC_Money else 0 end),
DO_SUM_SV=sum(case when unit=分钟 or unit=次 then DO_Money else 0 end),
TC_SUM_DG=sum(case when unit<>分钟 and unit<>次 then TC_Money else 0 end),
DO_SUM_DG=sum(case when unit<>分钟 and unit<>次 then DO_Money else 0 end)
from TC_Do_All
where DATEDIFF(dd, pay_T,2005-9-8)=0
group by EM_ID) as AA
left outer join (
select JS_EM_ID,JS_Money=sum(JS_Money)
from dan
where DATEDIFF(dd, pay_T,2005-9-8)=0
group by JS_EM_ID) as BB
on AA.EM_ID=BB.JS_EM_ID) as CC right outer join employee on employee.EM_ID=CC.EM_ID left outer join job on employee.job_id=job.job_id order by employee.EM_NO
字段名不能用变量
