Insert and update two different table using single store procedure in Sql

Insert and update two different table using single store procedure in Sql Server?

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;

Leave a comment

Your email address will not be published. Required fields are marked *