有没有人找到答案 - （列名或提供的值的数量与表定义不匹配） [英] does anyone findout the answer -(Column name or number of supplied values does not match table definition)

问题描述

alter function fz(@mdvn int,@fdate date,@tdate date)
returns @tabs table
(
	DVN int,
	PHC int,
	HSC int,
	ANC int,
	TT1 int,
	TTB int,
	IFA int,
	BP int,
	HB int
)
as
begin
declare @DVN int,@PHC int,@HSC int,@ANC int,@TT1 int,@TTB int,@IFA int,@BP int,@HB int
declare fnc cursor for
select dvn_cd,phc_cd,hsc_cd,sum(ANC1) as ANC,SUM(TT1) as TT1,sum(TTB2) as TT2,sum(IFA) as IFA,sum(BP1) as BP,sum(HB1) as HB from
(
select dvn_cd,phc_cd,hsc_cd,
    case when visit_no=3 and Visit_date between @fdate and @tdate then 1 else 0 end as ANC1,
    case when TTB=1 and TTDate between @fdate and @tdate then 1 else 0 end as TT1,
    case when TTB>1 and TTDate between @fdate and @tdate then 1 else 0 end as TTB2,
    case when IFA=100 and ANEDD between @fdate and @tdate then 1 else 0  end as IFA, 	 
    case when BP>='140/90' and ANEDD between @fdate and @tdate then 1 else 0  end as BP1,
    case when HB<11 and ANEDD between @fdate and @tdate then 1 else 0  end as HB1
from anvisits3 where dvn_cd=@mdvn and ANEDD between @fdate and @tdate
)a group by dvn_cd,phc_cd,hsc_cd
open fnc
	fetch next from fnc into @DVN,@PHC,@HSC,@ANC,@TT1,@TTB,@IFA,@BP,@HB 
	while @@fetch_status=0
	begin
		insert into @tabs
			select 'DVN'+convert(varchar(20),@DVN)+'PHC'+convert(varchar(20),@PHC)+'HSC'+convert(varchar(20),@HSC)+
			'ANC'+convert(varchar(20),@ANC)+'TT1'+Convert(varchar(20),@TT1)+'TTB'+convert(varchar(20),@TTB)+'IFA'+convert(varchar(20),@IFA)+
			'BP'+convert(varchar(20),@BP)+'HB'+convert(varchar(20),@HB)
		fetch next from fnc into @DVN,@PHC,@HSC,@ANC,@TT1,@TTB,@IFA,@BP,@HB 
	end
return
end

----------- -----

我的错误。

Msg 213，Level 16，State 1，Procedure fz，Line 33

列名或数字提供的值与表定义不匹配

。

----------------
my error.
Msg 213, Level 16, State 1, Procedure fz, Line 33
Column name or number of supplied values does not match table definition
.

推荐答案

在插入语句中提供的参数的数量是10而9 在函数定义中声明

导致错误。

//九参数

In insert statement no of supplied parameter is 10 while 9 is declared in function definition
resulting in error.
//Nine parameter

returns @tabs table
(
    DVN int,
    PHC int,
    HSC int,
    ANC int,
    TT1 int,
    TTB int,
    IFA int,
    BP int,
    HB int
)

// 1 parameter supplied here...
<pre lang="sql">insert into @tabs
            select &#39;DVN&#39;+convert(varchar(20),@DVN)+&#39;PHC&#39;+convert(varchar(20),@PHC)+&#39;HSC&#39;+convert(varchar(20),@HSC)+
            &#39;ANC&#39;+convert(varchar(20),@ANC)+&#39;TT1&#39;+Convert(varchar(20),@TT1)+&#39;TTB&#39;+convert(varchar(20),@TTB)+&#39;IFA&#39;+convert(varchar(20),@IFA)+
            &#39;BP&#39;+convert(varchar(20),@BP)+&#39;HB&#39;+convert(varchar(20),@HB)</pre>

更新：

Update with:

insert into @tabs
            select 'DVN'+convert(varchar(20),@DVN),'PHC'+convert(varchar(20),@PHC),'HSC'+convert(varchar(20),@HSC),
            'ANC'+convert(varchar(20),@ANC),'TT1'+Convert(varchar(20),@TT1),'TTB'+convert(varchar(20),@TTB),'IFA'+convert(varchar(20),@IFA),
            'BP'+convert(varchar(20),@BP),'HB'+convert(varchar(20),@HB)
        fetch next from fnc into @DVN,@PHC,@HSC,@ANC,@TT1,@TTB,@IFA,@BP,@HB

这篇关于有没有人找到答案 - （列名或提供的值的数量与表定义不匹配）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！