请任何人找到答案 [英] pls anyone findout the answer

问题描述

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 @IFA int
declare @BP int
declare @HB int
declare funcurz cursor static for
select dvn_cd,phc_cd,hsc_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
			select @mdv,@cfdate,@ctdate,@ANC,@TT1,@IFA,@BP,@HB
			  /* print 'DVN : '+ convert(varchar(20),@mdvn)+',FDT :'+convert(varchar(20),@fdate)+
				',TDT :'+convert(varchar(20),@tdate)+',RGDT:'+convert(varchar(20),@REGDT)+
				',ANC :'+convert(varchar(20),@ANC)+',TT1 :'+convert(varchar(20),@TT1)+
				',TT2 :'+convert(varchar(20),@TT2)+',IFA :'+convert(varchar(20),@IFA)+
				',BP :'+convert(varchar(20),@BP)+',HB:'+ convert(varchar(20),@HB)
   			 */	
   				fetch next from funcurz into @mdv,@cfdate,@ctdate,@ANC,@TT1,@IFA,@BP,@HB 
			END
end
close funcurz
deallocate funcurz
return  
end

消息16924，等级16，状态1，行1

Cursorfetch：The在INTO列表中声明的变量数必须与所选列的变量匹配。

Msg 16924, Level 16, State 1, Line 1
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

推荐答案

错误消息指示SELECT中的列必须与FETCH中的列匹配。 .INTO专栏列表。





此SELECT语句选择九列

The error message indicates that the columns in the SELECT must match the columns in the FETCH .. INTO column list.


This SELECT statement selects nine columns

select dvn_cd,phc_cd,hsc_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

此FETCH语句在INTO列列表中有八列

This FETCH statement has eight columns in the INTO column list

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

嗯。

消息很清楚：

在INTO列表中声明的变量数必须与所选列的变量数相匹配。

看看de funcurz的结果：

Um.
The message is pretty clear:
"The number of variables declared in the INTO list must match that of selected columns."
Look at the definition of funcurz:

declare funcurz cursor static for
select dvn_cd,
       phc_cd,
       hsc_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
...

并且共有9项。

当你拿到它们时：

and there are 9 items.
When you fetch them:

fetch next from funcurz into 
       @mdv,
       @cfdate,
       @ctdate,
       @ANC,
       @TT1,
       @IFA,
       @BP,
       @HB
    while @@FETCH_STATUS=0

只有8个。你必须有相同的号码！

There are only 8. You have to have the same number!

这篇关于请任何人找到答案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！