does anyone findout the answer -(Column name or number of supplied values does not match table definition)

Question

0.00/5 (No votes)

See more:

SQL

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

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

Posted 8-Jan-14 20:12pm

Member 10463904

Updated 8-Jan-14 20:21pm

Karthik_Mahalingam

v2

Add a Solution

Comments

TrushnaK 9-Jan-14 2:27am

Improve your question and add how you pass values to this function.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

er.rakesh · Answer 1 · 2014-01-08T20:38:00

Solution 1

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

XML

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:

SQL

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

Posted 8-Jan-14 20:38pm

er.rakesh

Updated 8-Jan-14 20:50pm

v2

Comments

Member 10463904 9-Jan-14 3:00am

alter function fzz(@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(IFA1) 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 IFA1,
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

SELECT * FROM fzz(40,'2011/01/01','2011/12/31')
---------------
i ubdated my query but now im getting the error as

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'DVN40' to data type int.

Christian Graus 9-Jan-14 17:33pm

The errors you are posting all mean EXACTLY what they say. Googling the exact error message will also give you help. We're happy to answer, but you need to be able to do some basic research of your own, and understand what you're doing enough to understand the error messages.

er.rakesh 9-Jan-14 23:54pm

your table structure should have varchar or char datatype instead of int specified

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