if exists (select * from dbo.sysobjects where [name]=@tableName )
begin
if(@calltype = '2') --全部 end as ANITel,case
set @select=@select+ N' Insert into #mycalldetail select case when SUBSTRING(ANI,0,3)=013 then SUBSTRING(ANI,1,7) when SUBSTRING(ANI,0,3)=015 then SUBSTRING(ANI,1,7) when SUBSTRING(ANI,0,2)=13 then SUBSTRING(ANI,0,7)
when SUBSTRING(ANI,0,2)=15 then SUBSTRING(ANI,0,7) when SUBSTRING(ANI,0,2)=01 and SUBSTRING(ANI,2,1)=0 then SUBSTRING(ANI,0,3) when SUBSTRING(ANI,0,2)=02 then SUBSTRING(ANI,0,3) else SUBSTRING(ANI,0,4) end as ANI,
case when SUBSTRING(DNI,0,3)=013 then SUBSTRING(DNI,1,7) when SUBSTRING(DNI,0,3)=015 then SUBSTRING(DNI,1,7) when SUBSTRING(DNI,0,2)=13 then SUBSTRING(DNI,0,7)
when SUBSTRING(DNI,0,2)=15 then SUBSTRING(DNI,0,7) when SUBSTRING(DNI,0,2)=01 and SUBSTRING(DNI,2,1)=0 then SUBSTRING(DNI,0,3) when SUBSTRING(DNI,0,2)=02 then SUBSTRING(DNI,0,3) else SUBSTRING(DNI,0,4) end as DNI from '+@tableName+' where CustID='+@CustID+' and ServerID='+@ServerID
else
set @select=@select+ N' Insert into #mycalldetail select case when SUBSTRING(ANI,0,3)=013 then SUBSTRING(ANI,1,7) when SUBSTRING(ANI,0,3)=015 then SUBSTRING(ANI,1,7) when SUBSTRING(ANI,0,2)=13 then SUBSTRING(ANI,0,7)
when SUBSTRING(ANI,0,2)=15 then SUBSTRING(ANI,0,7) when SUBSTRING(ANI,0,2)=01 and SUBSTRING(ANI,2,1)=0 then SUBSTRING(ANI,0,3) when SUBSTRING(ANI,0,2)=02 then SUBSTRING(ANI,0,3) else SUBSTRING(ANI,0,4) end as ANICode,
case when SUBSTRING(DNI,0,3)=013 then SUBSTRING(DNI,1,7) when SUBSTRING(DNI,0,3)=015 then SUBSTRING(DNI,1,7) when SUBSTRING(DNI,0,2)=13 then SUBSTRING(DNI,0,7)
when SUBSTRING(DNI,0,2)=15 then SUBSTRING(DNI,0,7) when SUBSTRING(DNI,0,2)=01 and SUBSTRING(DNI,2,1)=0 then SUBSTRING(DNI,0,3) when SUBSTRING(DNI,0,2)=02 then SUBSTRING(DNI,0,3) else SUBSTRING(DNI,0,4) end as DNICode from '+@tableName+' where CustID='+@CustID+' and ServerID='+@ServerID+' and CallType='+@calltype
set @begin=Dateadd(d,1,@begin)
end
else
begin
set @begin=Dateadd(d,1,@begin)
endset @select=@select+N'select * from #mycalldetail'
exec(@select)
运行结果:
1
2
2
2
2
2
2
2
ANI DNI
-------------- --------------
010 013
010 013
010
1346672 013
076 013
010
1346672 013
076 013
010 013
053 013
010 013
013
010 013
053 013
010 013
010 013
013 013
079 013
053 013
010 013
079 013
053 013
010
020 013
010 013
013 013
020 013
010 013
020 013
020 013
010 013
013 013
013
010
010 013
010 013
815 013
057 013
010
010 013
010 013
815 013
057 013
(43 行受影响)
(返回 43 行)
1346672结果正确,010的结果正确,其他的都不是我想要的了,013应该是1346672这样的,057应该是四位区号,帮我看看存储过程有什么错误,该怎么样写!!
以'013','13'开头的都是手机号,'013'的手机号从1位开始取,取7个位数,'13'的手机号从0位开始取,取7位
'01'开头第3位是0的,是座机号,取3位,'02'开头是座机号,取3位,其余是座机号,取4位