Solution :- Create procedure [dbo].[Sp_AddUserRating](@UserID bigint,@ProductID bigint,
@Rating decimal(18,2),@Comment nvarchar(50),@IP nvarchar(50),@Device varchar(50),
@OS varchar(50),@BrawserName nvarchar(50))
As
Begin
insert into tbl_ProductUserRating1(UserID,ProductID,Rating,Comment,IP,Device,OS,BrawserName)values
(@UserID,@ProductID,@Rating,@Comment,@IP,@Device,@OS,@BrawserName)
begin
if @Rating=5
update tbl_Product set Rate5=isnull(Rate5,0)+1,AvgRating=(isnull(Rate1,0)+1+isnull(Rate2,0)+1+isnull(Rate3,0)+isnull(Rate4,0)+isnull(Rate5,0))/5 WHERE ID=@ProductID;
else
if @Rating=4
update tbl_Product set Rate4=isnull(Rate4,0)+1,AvgRating=(isnull(Rate1,0)+1+isnull(Rate2,0)+1+isnull(Rate3,0)+isnull(Rate4,0)+isnull(Rate5,0))/5 WHERE ID=@ProductID;
else
if @Rating=3
update tbl_Product set Rate3=isnull(Rate3,0)+1,AvgRating=(isnull(Rate1,0)+1+isnull(Rate2,0)+1+isnull(Rate3,0)+isnull(Rate4,0)+isnull(Rate5,0))/5 WHERE ID=@ProductID;
else
if @Rating=2
update tbl_Product set Rate2=isnull(Rate2,0)+1,AvgRating=(isnull(Rate1,0)+1+isnull(Rate2,0)+1+isnull(Rate3,0)+isnull(Rate4,0)+isnull(Rate5,0))/5 WHERE ID=@ProductID;
else update tbl_Product set Rate1=isnull(Rate1,0)+1,AvgRating=(isnull(Rate1,0)+1+isnull(Rate2,0)+1+isnull(Rate3,0)+isnull(Rate4,0)+isnull(Rate5,0))/5 WHERE ID=@ProductID;
