CODEWITHSUNDEEP
sql-server
user3068863
user3068863

Reputation: 1

pls any one clear the error: 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

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

Upvotes: 0

Views: 102

Answers (3)

Mathese F
Mathese F

Reputation: 559

hello no it's not a problem of string to integer the error is different.

I doubt it's coming from the insert or the order or error is strange :)

First you cannot do an insert inside a function. Function can only do select

Then i count the number of parameter for your cursor and number of your select it seems ok but it could come from there. Why are you using a cursor? it makes no sense, do an insert based on a select and if it's easier to read use a with clause.

Example : 

with p as (select * from mytableA)
insert into myTableB 
select * from myTableA

Oh yes sorry, it's the morning :) you can do this kind of insert but why are u still using a cursor and a temporary table when you can only do a single select? You have a lots of useless statement there :)

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239714

I think you just need:

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
insert into @tabs (DVN,PHC,HSC,ANC,TT1,TTB,IFA,BP,HB)
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
return
end

And by the way, unless you're somehow being charged by the letter, I'd seriously recommend using more expressive names for your columns (both here and in the base tables), unless these acronyms are ubiquitous in your industry.

Upvotes: 1

Roman Pushkin
Roman Pushkin

Reputation: 6079

The error message itself is clear: looks like you're trying to insert into @tabs result with more columns the table itself has. For example, you may have 10 columns in the table, but trying to insert 11.

Another reason: you're trying to insert string data into the column with type integer.

Upvotes: 0

Related Questions