将varchar值'DVN40'转换为数据类型int时转换失败 [英] Conversion failed when converting the varchar value 'DVN40' to data type int

问题描述

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,@ANC4 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,sum(ANC4) as ANC4 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,
    case when visit_no=4 and Visit_date between @fdate and @tdate then 1 else 0 end as ANC4
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,@ANC4 
	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)+'ANC4'+convert(varchar(20),@ANC4)
        fetch next from fnc into @DVN,@PHC,@HSC,@ANC,@TT1,@TTB,@IFA,@BP,@HB,@ANC4
	end
return
end

-----------------
SELECT * FROM fzz(40,'2011/01/01','2011/12/31')
-----------------

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

推荐答案

alter function fzz(@mdvn int,@fdate date,@tdate date)
returns @tabs table
(
	DVN varchar(23),
        -- Leave the rest unchanged

您试图将varchar值存储在整数变量中;这显然是不可能的。

您必须更改@tabs表中DVN列的类型。

You are trying to store a varchar value in an integer variable; which is obviously not possible.
You have to change the type of the DVN column in the @tabs table.

@DVN 得到值40（因为 @mdvn 是40）然后在你的选择中

@DVN gets the value 40 (as @mdvn is 40) and then in your select

select 'DVN'+convert(varchar(20),@DVN)

你将值'DVN40'推回 @DVN 哪个是int !!!

you push the value 'DVN40' back to @DVN which is int!!!

这篇关于将varchar值'DVN40'转换为数据类型int时转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！