Syntax for SQL Server when either of parameter is passed

Question

If one of the paramater is passed / or both passed how to match that to single column in WHERE clause.

If both are passed should get result set just with the first one else with second one.

Ex:

--either will be passed
declare @processStepID int = null
declare @PROCESS_StepID int = null

if(@processStepID is null)
select * from CTL.CTRL.CTRL_PROCESS_STEP_ACTIVITIES 
where PROCESS_STEP_ID = @PROCESS_StepID
else
select * from CTL.CTRL.CTRL_PROCESS_STEP_ACTIVITIES 
where PROCESS_STEP_ID = @processStepID

How can this be written in where clause not using if statement.

thanks

Answer 1

select * from CTL.CTRL.CTRL_PROCESS_STEP_ACTIVITIES 
where (PROCESS_STEP_ID = @PROCESS_StepID) OR (PROCESS_STEP_ID = @processStepID)

Seems far too obvious. What am I missing?

Answer 2

Use the ISNULL function

SELECT *
FROM CTL.CTRL.CTRL_PROCESS_STEP_ACTIVITIES 
WHERE PROCESS_STEP_ID = ISNULL( @processStepID, @PROCESS_StepID )

Answer 3

select * 
from CTL.CTRL.CTRL_PROCESS_STEP_ACTIVITIES 
where PROCESS_STEP_ID = ISNULL(@PROCESS_StepID,@processStepID)

This should do what you are looking for

Answer 4

WHERE PROCESS_STEP_ID = coalesce( @processStepID, @PROCESS_StepID )