列名或所提供值的数目与表定义不匹配
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(@msgcontent)
from #HT_recemember where dbo.fc_checkmobiletype(mobile)='lt'
if @@error<>0
begin
set @ret=1
return
end
end
end
else if @flag='C' --电信
begin
if @tbflag='A'
begin
insert into jxtsms.dbo.Sms_Qf_WaitSendXlt1
(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)='xlt'
if @@error<>0
begin
set @ret=1
return
end
end
else
begin
insert into jxtsms.dbo.Sms_Qf_WaitSendXlt
(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)='xlt'
if @@error<>0
begin
set @ret=1
return
end
end
end
drop table #HT_recemember
END
执行这个存储过程的时候exec Qf_TeacherMessage 1201,5066,'A','B','6','测试消息，打扰了',1
会出现消息 213，级别 16，状态 1，第 1 行
插入错误: 列名或所提供值的数目与表定义不匹配。的问题这是怎么回事该怎么解决
[解决办法]
比如:有个表A,有字段a,b,你在插入表纪录的时候写成
insert A(a,b) values(xx,xx,xxx)
表只有2个列,你写了3个值进去,就会出现你描述的错误信息,具体是哪问题,你根据你的环境去检查一下就知道了。
[解决办法]
列名或所提供值的数目与表定义不匹配
检查下你的存储过程里面的语句
[解决办法]
insert(字段)跟values(字段)数量不一致
[解决办法]
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
@ReceDetail中选择出来的只有3列数据,
而你定影的表#HT_recemember中有4列,
所以不对应,所以出现错误.
insert into #HT_recemember select teacherid,mobile,path from jxtmsg.dbo.jxt_teacher where mobile <>....
