T-SQL Query 'Where' based on an If Statement

Question

Hello I have following exert from a SQL server stored procedure:

SET @GenderToSearchBy =
        CASE 
            When @Gender IS NOT NULL And @Gender='M' THEN @MaleID
            When @Gender IS NOT NULL And @Gender='F' Then @FemaleID
            Else NULL
        End

SELECT FRST_NAME,LAST_NAME,ID_NUMB,GRDE_DSCN,
GEND_ID FROM #TEMP_STUD WHERE GEND_ID=@GenderToSearchBy

Now, I want this to work that if @Gender is NULL or not equal to M or F then search both genders, meaning that WHERE isn't required, is there any way to do that without writing 2 separate queries inside a CASE?

Answer 1

try using COALESCE

WHERE GEND_ID = COALESCE(@GenderToSearchBy, GEND_ID)

or ISNULL

WHERE GEND_ID = ISNULL(@GenderToSearchBy, GEND_ID)

• COALESCE()
• ISNULL()