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
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