stored procedure with case and actions

Intoduction :-

Hii Friends...

Today I explain how to use case and action in stored procedure . 


CREATE Proc [dbo].[Proc_ManageRatingAndReview]--exec [Proc_ManageRatingAndReview] 'GetAllBySeller','','','101647'                
(                           
@Action nvarchar(50)='',                           
@ID int=0,       
@ProductID int=0,       
@SellerMsrNo int=0                          
)                           
as                           
begin                           
                            
if(@Action='Delete')                           
begin                           
delete tblRatingAndReview Where RatingAndReviewID = @ID                           
end                           
                            
                                 
if(@Action='IsActive')                           
begin                           
update tblRatingAndReview set IsActive=case when IsActive=0 then 1 else 0 end                            
where RatingAndReviewID=@ID                           
end                           
                            
                            
if(@Action='GetAll')                           
begin                           
Select * from tblRatingAndReview                            
where RatingAndReviewID= case when @ID=0 then RatingAndReviewID  else @ID  end  order by ProductID desc                           
end                         
                            
                         
if(@Action='Get')                           
begin                           
Select * from tblRatingAndReview                           
where RatingAndReviewID= case when @ID=0 then RatingAndReviewID else @ID  end and  IsActive='true'  order by ProductID desc                               
end      
     
if(@Action='GetAllBySeller')                           
begin                           
Select distinct IV.ProductName,RAR.*,MM.FirstName+' '+MM.LastName as SellerName from tblRatingAndReview as RAR    
inner join tblInvoiceDetail as IV on RAR.ProductID=IV.ProductID    
inner join tblMlm_memberMaster as MM on RAR.SellerMsrNo= MM.MsrNo                        
where RAR.SellerMsrNo= case when @SellerMsrNo=0 then RAR.SellerMsrNo  else @SellerMsrNo  end  order by RAR.ProductID desc                           
end    
                          
                       
                       
if(@Action='GetByProductID')                           
begin                           
Select RatingAndReviewID, ReviewTitle, YourReview, CONVERT(decimal(5,0),YourRating)as YourRating,IPAddress, ProductID, UserName, UserEmail, AddDate, LastUpdate, IsActive from tblRatingAndReview                           
where ProductID= case when @ProductID=0 then ProductID else @ProductID  end       
and SellerMsrNo= case when @SellerMsrNo=0 then SellerMsrNo else @SellerMsrNo  end       
and  IsActive='true' order by ProductID,AddDate desc                               
end                         
                   
                   
if(@Action='GetRating')                       
begin                       
                   
declare @Rating decimal(5,0)                   
select @Rating=ROUND(AVG(YourRating), 0) FROM tblRatingAndReview where ProductID=                   
 case when @ID=0 then ProductID                   
 else @ID                   
 end and  IsActive='true'                   
                   
if @Rating>0                   
begin                    
 select @Rating as Rating                   
end                   
else                   
begin                   
 select 4 as Rating                   
end                   
                   
                   
end                               
end