Can @parameter IS NULL slow down the execution of query?

Option 1:

CREATE PROCEDURE [dbo].[GetStudents]
@MinimumAge int = NULL
AS

select * from Students s where @MinimumAge is null or s.Age >= @MinimumAge

Option 2:

CREATE PROCEDURE [dbo].[GetStudents]
@MinimumAge int = NULL
AS

IF @MinimumAge IS NULL
BEGIN
    select * from Students s
END
ELSE
BEGIN
    select * from Students s where s.Age >= @MinimumAge
END

Will option 1 be slower than option 2 because it has extra WHERE clause? Or will SQL Server take care of that ?

I think option 1 is good as I don't have to duplicate the code, unless it's slower. The original procedure has many lines of code. So I don't want to duplicate all code just for one condition, unless it's the only good option.

There is indexing on the age column and it does not allow nulls.

The problem is, Actual SP is having many lines of code - So I don't want to duplicate all code just to add one where condition, unless it's the only good option.

Community wiki answer:

You could also try:

WHERE Age >= COALESCE(@MnimumAge, 0)

Assuming 0 is not a valid Age. Otherwise, you could use -1 as a default instead of 0.

This will allow a seek on an index keyed on Age.

The other main option is to add OPTION (RECOMPILE) to the statement:

CREATE PROCEDURE [dbo].[GetStudents]
    @MinimumAge int = NULL
AS
BEGIN
    SELECT S.* 
    FROM dbo.Students AS S
    WHERE @MinimumAge IS NULL
    OR S.Age >= @MinimumAge
    OPTION (RECOMPILE);
END;

This adds a small overhead on each call as the statement (not whole procedure) is recompiled, but it allows the parameter embedding optimization, so you will get an optimal execution plan for the specific value of @MinimumAge on each execution. See Parameter Sniffing, Embedding, and the RECOMPILE Options by Paul White for more information.

You might also like to read #BackToBasics : An Updated "Kitchen Sink" Example by Aaron Bertrand and review the related Q & A SQL Server--If logic in stored procedure and the plan cache

StackExchange Database Administrators Q#208824, answer score: 5