Click here to Skip to main content
15,891,649 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
This is my code...
SQL
alter function anf
(
@mdvn int,
@fdate datetime,
@tdate datetime
)
returns int
as
begin
declare @vdate date
declare @fedddate date
declare @tedddate date
declare @tvdate 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 @REGDT int
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,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(BP) as BP, SUM(HB) as HB from
(
	select dvn_cd,phc_cd,hsc_cd,
	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 BP,
	case when HB<11 and ANEDD between @fdate and @tdate then 1 else 0  end as HB
	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 @mdvn,@REGDT,@ANC,@TT1,@TT2,@IFA,@BP,@HB 
		while @@FETCH_STATUS=0
		   begin
				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 @mdvn,@REGDT,@ANC,@TT1,@TT2,@IFA,@BP,@HB 
			END
end
close funcurz
deallocate funcurz
return (int)
end


------------------------------------------------------------------------------------------------

i got the error as


Msg 443, Level 16, State 14, Procedure anf, Line 45
Invalid use of a side-effecting operator 'PRINT' within a function.
Msg 207, Level 16, State 1, Procedure anf, Line 55
Invalid column name 'int'.
Posted
Updated 12-Dec-13 23:14pm
v2

1 solution

Which part of "Invalid use of a side-effecting operator 'PRINT' within a function." do you have difficulties with? It says you can't use functions with side effects in a UDF.
UDF's have to be deterministic so they are not allowed to use any functions that produce side effects. Feel free to read on here: http://technet.microsoft.com/en-us/library/ms187440%28v=sql.105%29.aspx[^].

Regards,
— Manfred
 
Share this answer
 
v2
Comments
Maciej Los 13-Dec-13 5:16am    
+5!
Manfred Rudolf Bihy 13-Dec-13 5:46am    
Thank you, Maciej!

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900