Tuesday, July 16, 2013

Procedure which accepts parameters and checks if the parameter is not 0 or null ,gets result based on parameter passed

//This Procedure accepts 5 parameters ,even if null or 0 is passed of any parameters it returs result.
//
Create procedure usp_testsp
@ChannelID nvarchar(100),     
@DealerID nvarchar(100),       
     
@AreaID nvarchar(100),       
     
@ZoneID nvarchar(100),      
     
@RoleID nvarchar(100)     
     
     
as     
     
SELECT Distinct MZ.ZONE,MA.AREANAME as AO,D.Code as DealerCode,D.Name as DealerName,MP1.EmployeeCode,     
(MP1.FirstName + ' ' + MP1.LastName) as Name,MS.SkillName as empRole,MT.Code,MPT.TrainingCompletionDate
FROM MAS_AREA MA        
INNER JOIN MAS_ZONE MZ ON MA.ZONEID=MZ.ID      
      
INNER JOIN DEALER D ON MA.ID=D.AREACODEID       
INNER JOIN DEALERLOCATION  DL ON D.ID=DL.DEALERID  and DL.IsActive=1     
INNER JOIN DEALERCHANNELMAPPING DM ON D.ID=DM.DEALERID and D.IsActive=1     
inner join ManPowerTraining MPT on DL.ID=MPT.ManPowerId and MPT.IsActive=1     
INNER JOIN LOCATIONMANPOWERASSOCIATON LMA ON LMA.MANPOWERID = MPT.MANPOWERID and  LMA.ENDDATE IS NULL       
inner join  ManPower MP1 on MP1.ID=MPT.ManPowerId and MP1.IsActive=1     
inner join  ManPowerExperience MPE1 on MP1.ID=MPE1.ManPowerId and MPE1.IsActive=1     
inner join  Mas_Skills MS on MS.ID=LMA.SkillID and MS.IsActive=1     
inner join Mas_Training MT on MT.ID=MPT.TrainingId and MT.IsActive=1    
where   
       
   
 (@RoleID=0 OR @RoleID=NULL OR MPE1.SKILLID =@RoleID)   
AND DM.CHANNELID=@ChannelID     
 AND (@AREAID=0 OR @AREAID=NULL OR MA.ID=@AREAID)     
 AND (@DEALERID=0 OR @DEALERID= NULL OR D.ID=@DEALERID)     
AND (@ZONEID=0 OR @ZONEID=NULL OR MZ.ID=@ZONEID)    

order BY MZ.ZONE,MA.AREANAME

No comments:

Post a Comment