Why come this error in sql fuction
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