Hi,
Check the following Query... use OR Operator for this simple checking.
Declare @RRPTDemandStatus char(7) = null
select mpd.MPD_DemandStatus, m.Description,* from RPT_MRFProgressDetails mpd
left outer join MasterData M on mpd.MPD_DemandStatus = M.Id
where ((mpd.MPD_DemandStatus = @RRPTDemandStatus AND @RRPTDemandStatus IS NOT NULL) OR @RRPTDemandStatus IS NULL)
Regards,
GVPrabu