SQL dynamic where clause with using exce in select statement

declare @RoleID varchar(10)=''

,@Roleid1 varchar(10)=''

,@RID varchar(10)='1'

select * from(select rid,username,roleid from tbl_user_registration) dt

------------ If need to use Equal(=) then

where rid = iif(len(isnull(@RID,''))=0, RID , @RID)

--------- Result---------




============================================================

declare @RoleID varchar(10)='20' ,@Roleid1 varchar(10)='30',@RID varchar(10)='1'

select * from(select rid,username,roleid from tbl_user_registration) dt

------------ If need to use between then

where roleid between iif(len(isnull(@RoleID,''))=0, roleid , @RoleID) and iif(len(isnull(@RoleID1,''))=0, roleid , @RoleID1)

----------------------- Result

=====================================================================================

declare @RoleID varchar(10)='20' ,@Roleid1 varchar(10)='30',@RID varchar(10)='1'

select * from(select rid,username,roleid from tbl_user_registration) dt

------------ If need to use IFF inside IIF then

where RID = iif(len(isnull(@RID,''))=0, RID , iif(@RID =2, RID,iif(@RID=1,@RID,RID)))

----------------------- Result