有这样的一个存储过程,
ALTER PROCEDURE [dbo].[Proc_Search_CallOut_Detail123]
@in_time_s VARCHAR(19),
@in_time_e VARCHAR(19),
@in_paid Varchar(100),
@in_usrloginid Varchar(500),
@int_callstate varchar(10)
AS
BEGIN
SET NOCOUNT ON;
If Object_id('tempdb..tmp1') Is Not Null Drop Table #tmp1;
If Object_id('tempdb..tmp_usr') Is Not Null Drop Table #tmp_usr;
declare @state varchar(1000);
declare @sql varchar(1000);
Set @in_paid=dbo.GetPara(@in_paid);
Set @in_usrloginid=dbo.GetPara(@in_usrloginid); Select b.pd_paid pa_id,a.usr_id,a.usr_loginid
Into #tmp_usr
From sys_user a,sys_paradept b
Where a.usr_pdid=b.pd_id
And (@in_paid='' OR ','+@in_paid+',' Like '%,'+Cast(pd_paid as varchar)+',%')
And (@in_usrloginid='' OR ','+@in_usrloginid+',' Like '%,'+usr_loginid+',%');
SELECT
c.pa_id,
CONVERT(VARCHAR(19),a.begintime,120) begintime
,b.userid callingno
,a.calledno
,dbo.GetCharSecond(dbo.GetValue(a.sc)) sc
,CONVERT(VARCHAR(19),a.endtime,120) endtime
Into #tmp1
FROM call_log a,call_agent_log b,#tmp_usr c
WHERE a.sessionid=b.sessionid
AND a.calltype=1
And b.queuetype=4
And b.userid=c.usr_loginid
AND CONVERT(VARCHAR(19),a.begintime,120) BETWEEN @in_time_s AND @in_time_e
Insert into #tmp1
Select
d.pa_id,
CONVERT(VARCHAR(19),a.begintime,120),
c.userid,
b.userid,
dbo.GetCharSecond(dbo.GetValue(a.sc)),
CONVERT(VARCHAR(19),a.endtime,120)
From call_log a,call_agent_log b,agentstate_log c,#tmp_usr d
Where a.sessionid=b.sessionid
And b.callbegintime=c.statetime
And b.queuetype=6
And c.state=6
And b.agentid<>c.agentid
And c.userid=d.usr_loginid
AND CONVERT(VARCHAR(19),a.begintime,120) BETWEEN @in_time_s AND @in_time_e
Select * From #tmp1 Order By begintime;
If Object_id('tempdb..tmp1') Is Not Null Drop Table #tmp1;
If Object_id('tempdb..tmp_usr') Is Not Null Drop Table #tmp_usr;
END@int_callstate 有2个值(1或者是2,或者是1,2),根据输入的这个条件来判断如果@int_callstate=1,a.sc>0(成功),如果@int_callstate=1,a.sc=0(失败),如果@int_callstate='1,2',1=1(全部),请问高手怎么来根据输入的@int_callstate状态来相应地改变a.sc这个条件呢
ALTER PROCEDURE [dbo].[Proc_Search_CallOut_Detail123]
@in_time_s VARCHAR(19),
@in_time_e VARCHAR(19),
@in_paid Varchar(100),
@in_usrloginid Varchar(500),
@int_callstate varchar(10)
AS
BEGIN
SET NOCOUNT ON;
If Object_id('tempdb..tmp1') Is Not Null Drop Table #tmp1;
If Object_id('tempdb..tmp_usr') Is Not Null Drop Table #tmp_usr;
declare @state varchar(1000);
declare @sql varchar(1000);
Set @in_paid=dbo.GetPara(@in_paid);
Set @in_usrloginid=dbo.GetPara(@in_usrloginid); Select b.pd_paid pa_id,a.usr_id,a.usr_loginid
Into #tmp_usr
From sys_user a,sys_paradept b
Where a.usr_pdid=b.pd_id
And (@in_paid='' OR ','+@in_paid+',' Like '%,'+Cast(pd_paid as varchar)+',%')
And (@in_usrloginid='' OR ','+@in_usrloginid+',' Like '%,'+usr_loginid+',%');
SELECT
c.pa_id,
CONVERT(VARCHAR(19),a.begintime,120) begintime
,b.userid callingno
,a.calledno
,dbo.GetCharSecond(dbo.GetValue(a.sc)) sc
,CONVERT(VARCHAR(19),a.endtime,120) endtime
Into #tmp1
FROM call_log a,call_agent_log b,#tmp_usr c
WHERE a.sessionid=b.sessionid
AND a.calltype=1
And b.queuetype=4
And b.userid=c.usr_loginid
AND CONVERT(VARCHAR(19),a.begintime,120) BETWEEN @in_time_s AND @in_time_e
Insert into #tmp1
Select
d.pa_id,
CONVERT(VARCHAR(19),a.begintime,120),
c.userid,
b.userid,
dbo.GetCharSecond(dbo.GetValue(a.sc)),
CONVERT(VARCHAR(19),a.endtime,120)
From call_log a,call_agent_log b,agentstate_log c,#tmp_usr d
Where a.sessionid=b.sessionid
And b.callbegintime=c.statetime
And b.queuetype=6
And c.state=6
And b.agentid<>c.agentid
And c.userid=d.usr_loginid
AND CONVERT(VARCHAR(19),a.begintime,120) BETWEEN @in_time_s AND @in_time_e
Select * From #tmp1 Order By begintime;
If Object_id('tempdb..tmp1') Is Not Null Drop Table #tmp1;
If Object_id('tempdb..tmp_usr') Is Not Null Drop Table #tmp_usr;
END@int_callstate 有2个值(1或者是2,或者是1,2),根据输入的这个条件来判断如果@int_callstate=1,a.sc>0(成功),如果@int_callstate=1,a.sc=0(失败),如果@int_callstate='1,2',1=1(全部),请问高手怎么来根据输入的@int_callstate状态来相应地改变a.sc这个条件呢
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货