日期:2014-05-18  浏览次数:21371 次

列名或所提供值的数目与表定义不匹配
ALTER PROCEDURE [dbo].[Qf_TeacherMessage]
@areaid int,
@schid int =null,
@flag varchar(50),--号码类型
@fee varchar(4),--筛选号码
@spn varchar(50),
@msgcontent varchar(100),
@ret int output
AS
SET NOCOUNT ON;
BEGIN
  set @ret=0
declare @ReceDetail varchar(1000)
set @recedetail='select teacherid,mobile,path from jxtmsg.dbo.jxt_teacher where mobile<>'''' and '
if @areaid is not null
begin
set @recedetail=@recedetail+' left(areaid,4)='+Convert(varchar,@areaid)+''
end
else
begin
set @ret=1
return
end
if @schid is not null
begin
set @recedetail=@recedetail+' and schid='+Convert(varchar,@schid)+''
end


declare @newReceDetail varchar(4500)
create table #HT_recemember(teacherid varchar(15),teachername varchar(10), mobile varchar(15),path varchar(10))--创建存在接收人对象的临时表
set @newReceDetail='insert into #HT_recemember '+@ReceDetail


execute(@newReceDetail) --将接收人分解到HT_recemember,使用后将删除

  if not exists(select top 1 *From #HT_recemember)
return
select top 10 * from jxt_teacher
  if @fee='A'
begin
  --发送到所有老师
  select * from #HT_recemember
   
end
  else if @fee='B'
  begin
  -- 只发领导
  delete #HT_recemember where mobile not in
(select mobile from jxt_schoolleader)
  end
  else if @fee='C'
  begin 
  --只发班主任
  delete #HT_recemember where mobile not in
  (select a.mobile from #HT_recemember a,jxt_classRant b where a.teacherid=b.teacherid and b.RantType=0)
  end

  declare @tbflag varchar(2)
  select top 1 @tbflag=Flag from jxtsms.dbo.Sms_Control



  if @flag='A' --移动
begin
if @tbflag='A'
begin
insert into jxtsms.dbo.Sms_Qf_WaitSendYd1 
(mobile,spn,path,msgcontent,status,sysflag,submittime,settime,jb,msglen)
select distinct(mobile),@spn,path,replace(replace(@msgcontent,N'%teachername',teachername),N'%teacherid',teacherid),0,'sf',getdate(),getdate(),4,len(@msgcontent) 
from #HT_recemember where dbo.fc_checkmobiletype(mobile)='yd' 
if @@error<>0
begin
set @ret=1
return
end
end
else
begin
insert into jxtsms.dbo.Sms_Qf_WaitSendYd
(mobile,spn,path,msgcontent,status,sysflag,submittime,settime,jb,msglen)
select distinct(mobile),@spn,path,replace(replace(@msgcontent,N'%teachername',teachername),N'%teacherid',teacherid),0,'sf',getdate(),getdate(),4,len(@msgcontent) 
from #HT_recemember where dbo.fc_checkmobiletype(mobile)='yd' 
if @@error<>0
begin
set @ret=1
return
end
end
end
else if @flag='B' --联通
begin
if @tbflag='A'
begin
insert into jxtsms.dbo.Sms_Qf_WaitSendLt1 
(mobile,spn,path,msgcontent,status,sysflag,submittime,settime,jb,msglen)
select distinct(mobile),@spn,path,replace(replace(@msgcontent,N'%teachername',teachername),N'%teacherid',teacherid),0,'sf',getdate(),getdate(),4,len(@msgcontent) 
from #HT_recemember where dbo.fc_checkmobiletype(mobile)='lt' 
if @@error<>0
begin
set @ret=1
return
end
end
else
begin
insert into jxtsms.dbo.Sms_Qf_WaitSendLt
(mobile,spn,path,msgcontent,status,sysflag,submittime,settime,jb,msglen)
select distinct(mobile),@spn,path,replace(replace(@msgcontent,N'%teachername',teachername),N'%teacherid',teacherid),0,'sf',getdate(),getdate(),4,len(@msgconten