CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Art F
Art F

Reputation: 4202

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

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?

Upvotes: 3

Views: 114

Answers (1)

John Woo
John Woo

Reputation: 263693

try using COALESCE

WHERE GEND_ID = COALESCE(@GenderToSearchBy, GEND_ID)

or ISNULL

WHERE GEND_ID = ISNULL(@GenderToSearchBy, GEND_ID)

Upvotes: 2

Related Questions