帮我看看这里的存储过程里面的参数该怎么传递!谢谢了!

努力偷懒 2005-09-08 10:15:18
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
...全文
123 3 打赏 收藏 举报
写回复
3 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
努力偷懒 2005-09-08
把存储过程写成下面就行了!
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 '
exec(@strSQL)
  • 打赏
  • 举报
回复
努力偷懒 2005-09-08
不是字段名变量来的哦!是一个搜索条件来的啊!
  • 打赏
  • 举报
回复
vivianfdlpw 2005-09-08
字段名不能用变量
  • 打赏
  • 举报
回复
相关推荐
发帖
MS-SQL Server
加入

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2005-09-08 10:15
社区公告
暂无公告