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

Question

1.00/5 (2 votes)

See more:

SQL

USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[func2]    Script Date: 12/24/2013 17:35:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[func2]
(@mdvn int,@fdate date,@tdate date)
returns @funcurz2 table
(
mdv int, fdate date, tdate date,ANC int,
TT1 int,IFA int,BP int,HB int
)
as
begin
declare @vdate date
declare @tvdate date
declare @fedddate date
declare @tedddate date
set @vdate=dateadd(dd,-91,@fdate)
set @tvdate=dateadd(dd,36,@tdate)
set @fedddate=dateadd(dd,20,@fdate)
set @tedddate=dateadd(dd,310,@tdate)
declare @mdv int
declare @cfdate date
declare @ctdate date
declare @ANC int
declare @TT1 int
--declare @TT2 int
declare @IFA int
declare @BP int
declare @HB int
declare funcurz cursor static for
select dvn_cd, sum(REGDT) as Regdt,SUM(ANC) as ANC,SUM(TT1) as TT1, SUM(TT2)as TT2,
SUM(IFA) as IFA, SUM(BP1) as BP1, SUM(HB1) as HB1 from
(
	select dvn_cd,phc_cd,hsc_cd,Visit_Date,TTDate,anc_fullId,TTB,IFADate,BP,HB,
	case when ANEDD IS null then 0 else 1 end as REGDT,
	case when visit_no=3 and ANEDD between @vdate and @tvdate then 1 else 0  end as ANC, 	 
	case when TTB=1 and ANEDD between @fdate and @tdate then 1 else 0  end as TT1, 	 
	case when TTB>2 and ANEDD between @fdate and @tdate then 1 else 0  end as TT2, 	 
	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 a where DVN_CD=@mdvn and ANEDD between @fedddate and @tedddate
)a group by dvn_cd,phc_cd,hsc_cd
open funcurz
begin
	fetch next from funcurz into @mdv,@cfdate,@ctdate,@ANC,@TT1,@IFA,@BP,@HB 
		while @@FETCH_STATUS=0
		   begin
			insert into @funcurz2--(@mdv,@cfdate,@ctdate,@ANC,@TT1,@TT2,@IFA,@BP,@HB)
			select 'DVN : '+ @mdvn +',FDT :'+convert(varchar(20),@fdate)+',TDT :'+convert(varchar(20),@tdate)+',ANC :'+@ANC+',TT1 :'+@TT1+
				',IFA :'+@IFA+	',BP :'+@BP+',HB:'+ @HB  
   			fetch next from funcurz into @mdv,@cfdate,@ctdate,@ANC,@TT1,@IFA,@BP,@HB 
			END
end
close funcurz
deallocate funcurz
return  
end

Msg 213, Level 16, State 1, Procedure func2, Line 46
Column name or number of supplied values does not match table definition.

Posted 24-Dec-13 2:20am

Member 10463904

Updated 24-Dec-13 2:28am

Maciej Los

v2

Add a Solution

2 solutions

Solution 1

SQL

USE [master]
GO
/****** Object: UserDefinedFunction [dbo].[func2] Script Date: 12/24/2013 17:35:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[func2]
(@mdvn int,@fdate date,@tdate date)
returns @funcurz2 table
(
mdv int, fdate date, tdate date,ANC int,
TT1 int,IFA int,BP int,HB int
)
as
begin
declare @vdate date
declare @tvdate date
declare @fedddate date
declare @tedddate date
set @vdate=dateadd(dd,-91,@fdate)
set @tvdate=dateadd(dd,36,@tdate)
set @fedddate=dateadd(dd,20,@fdate)
set @tedddate=dateadd(dd,310,@tdate)
declare @mdv int
declare @cfdate date
declare @ctdate date
declare @ANC int
declare @TT1 int
--declare @TT2 int
declare @IFA int
declare @BP int
declare @HB int
declare funcurz cursor static for
select dvn_cd, sum(REGDT) as Regdt,SUM(ANC) as ANC,SUM(TT1) as TT1, SUM(TT2)as TT2,
SUM(IFA) as IFA, SUM(BP1) as BP1, SUM(HB1) as HB1 from
(
select dvn_cd,phc_cd,hsc_cd,Visit_Date,TTDate,anc_fullId,TTB,IFADate,BP,HB,
case when ANEDD IS null then 0 else 1 end as REGDT,
case when visit_no=3 and ANEDD between @vdate and @tvdate then 1 else 0 end as ANC, 
case when TTB=1 and ANEDD between @fdate and @tdate then 1 else 0 end as TT1, 
case when TTB>2 and ANEDD between @fdate and @tdate then 1 else 0 end as TT2, 
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 a where DVN_CD=@mdvn and ANEDD between @fedddate and @tedddate
)a group by dvn_cd,phc_cd,hsc_cd
open funcurz
begin
fetch next from funcurz into @mdv,@ANC,@TT1,@IFA,@BP,@HB 
while @@FETCH_STATUS=0
begin
insert into @funcurz2(@mdv,@cfdate,@ctdate,@ANC,@TT1,@TT2,@IFA,@BP,@HB)
--select 'DVN : '+ @mdvn +',FDT :'+convert(varchar(20),@fdate)+',TDT :'+convert(varchar(20),@tdate)+',ANC :'+@ANC+',TT1 :'+@TT1+
--',IFA :'+@IFA+	',BP :'+@BP+',HB:'+ @HB 
fetch next from funcurz into @mdv,@ANC,@TT1,@IFA,@BP,@HB 
END
end
close funcurz
deallocate funcurz
return 
end


-----what was the actual format and how you want to display

Posted 24-Dec-13 2:37am

Renuka Ruke

Comments

CHill60 24-Dec-13 9:32am

It might be nice to explain how you've changed the code and why. I can't work out how this would solve the OPs problem

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)

CHill60 · Accepted Answer · 2013-12-24T03:45:00

You have a discrepancy between your cursor declaration and the fetch
i.e. the

SQL

declare funcurz cursor static for select ...etc

and

next from funcurz into @mdv,@cfdate,@ctdate,@ANC,@TT1,@IFA,@BP,@HB

don't match up

The mapping currently is

dvn_cd --> @mdv
sum(REGDT) as Regdt --> @cfdate
SUM(ANC) as ANC --> @ctdate
SUM(TT1) as TT1 --> @ANC
SUM(TT2)as TT2 --> @TT1
SUM(IFA) as IFA --> @IFA
SUM(BP1) as BP1 --> @BP
SUM(HB1) as HB1 --> @HB

but you've defined @cfdate and @ctdate as date - doesn't seem right to be using SUM

Next look at your schema for table funcurz2 and check that the types of values that you are trying to insert match the column types in your table in the order they are defined. If you are adding them in a different order OR if there are more columns in that table than are in your insert statement then you need to define which columns you are inserting