Dharmesh Sharma

Dharmesh Sharma

  • NA
  • 905
  • 918.7k

Why come this error in sql fuction

Dec 29 2011 1:34 AM
hello,

this is my function

ALTER FUNCTION [softtech].[UDF_Get_month_wise_rate] (@month int,@rest_id int,@menuid int)
RETURNS int

AS
BEGIN
     DECLARE    @couter        int,@avgrating        int,@totalrating    int
 

Insert Into temp([id],[rest_id],[menu],[userid],[username],[comments],[date],[rating]) SELECT *  FROM [foodpoodle].[softtech].[comments] where rest_id=@rest_id AND menu=@menuid AND Year([date])=YEAR(getdate())
Insert Into temp1([id],[rest_id],[menu],[userid],[username],[comments],[date],[rating]) SELECT * from temp where Month(date)=@month
SELECT @couter=COUNT(id) FROM temp1
SELECT @totalrating=SUM(rating) from temp1
SET @avgrating=@totalrating/@couter
return @avgrating
END


and when is compile i am get this error

Msg 443, Level 16, State 15, Procedure UDF_Get_month_wise_rate, Line 14
Invalid use of a side-effecting operator 'INSERT' within a function.
Msg 443, Level 16, State 15, Procedure UDF_Get_month_wise_rate, Line 15
Invalid use of a side-effecting operator 'INSERT' within a function.

so Select with where condition not working in SQL User define function
and thease two line work in store producere
so why come this error


Answers (3)