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