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

Read More

Some tricks for stored procedures

Intoduction :-

Hii Friends...


Today I give some tricks that help in stored procedures when we are fixed. Like when we not found any stored procedure , or we alter any stored procedure then what we do.

 Trick 1 :-    when procedure not found then we write this query only . With the help of this query you can find any stored procedure that you want.


select * from sys.objects where name like '%yourSearchText'   

In this you only change the Stored procedure name that you want  and you find it.


Trick 2 :-    when procedure goes alter then recovery process is given blow .


SELECT deqs.last_execution_time AS [Time], dest.text AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC    


with the help of this query you can recover your sp.

Read More