Rusty T-SQL knowledge and conditional login in where clause

Question

I haven't touched SQL Server and SQL queries for over 5 years and now I need to write a new stored procedure today.

Many of these stored procedure parameters are optional thus there are potentially different execution plans and paths for every execution. I wonder if I don't do this right it will be horribly slow and likely an anti-pattern so I want to be careful here.

The following is pseudo-code of what I'm trying to accomplish.

ALTER PROCEDURE GetPatients
    @Active BIT,
    @PatientGender NVARCHAR(1),
    @PatientName NVARCHAR(50),
    @PatientDOB DATE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * 
    FROM PatientTable
    WHERE Active = @Active

    --@PatientGender could be M, F or B for both. 
    --If B then disregard the values in the PatientGender Column
    AND (CASE @PatientGender
          WHEN 'M' THEN PatientGender = 'M'
          WHEN 'F' THEN PatientGender = 'F'
          ELSE
          END)

    --@PatientName is a wildcard search
    --@PatientName is null then disregard the values in the PatientName column
    AND (CASE @PatientName
          WHEN '' THEN --don't include this and branch
          ELSE PatientName LIKE '%@PatientName%'
    END)

    --PatientDOB same as above. Not included here for brevity but if empty skip if 
    present add an AND branch to WHERE clause.

END
GO

So what I've come up with is these two solutions and I have a feeling neither of them are really any good.

First is an ugly branching stored procedure .....but it works.

ALTER PROCEDURE GetPatients 
  @Active BIT,
  @PatientGender NVARCHAR(1),
  @PatientName NVARCHAR(50),
  @PatientDOB DATE
AS
BEGIN
  SET NOCOUNT ON;

  IF @PatientGender = 'M'
  BEGIN
    SELECT * 
    FROM PatientTable 
    WHERE Active = @Active 
    AND PatientGender = 'M'
 END

IF @PatientGender = 'F'
BEGIN
    SELECT * 
    FROM PatientTable
    WHERE Active = @Active
    AND PatientGender = 'F'
END
ELSE
BEGIN
    SELECT * 
    FROM PatientTable 
    WHERE Active = @Active
END

--and so it keeps going till every condition is covered
--it's ugly

END

Second is a dynamic SQL and while this "looks" better but if I remember correctly you should avoid dynamic SQL unless no other option.

ALTER PROCEDURE GetPatients 
  @Active BIT,
  @PatientGender NVARCHAR(1),
  @PatientName NVARCHAR(50),
  @PatientDOB DATE
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @SQL NVARCHAR(1000)
  DECLARE @PatientGenderFilter NVARCHAR(500)
  DECLARE @PatientNameFilter NVARCHAR(500)
  DECLARE @PatientDOBFilter NVARCHAR(500)


  SET @PatientGenderFilter = CASE
    WHEN @PatientGender = 'M' THEN N'AND PatientGender = ''M'' '
    WHEN @PatientGender = 'F' THEN N'AND PatientGender = ''F'' '
  END

  SET @PatientNameFilter = CASE @PatientName
    WHEN '' THEN N''
    ELSE N'AND PatientName LIKE ''%' + @PatientName + '%'' '
  END

SET @SQL = N'SELECT * FROM PatientTable WHERE Active = ' + CAST(@Active AS CHAR(1)) + ' ' + @PatientGenderFilter + @PatientNameFilter 

--PRINT @SQL
EXEC(@SQL)

END
GO

So my question is: I have two working queries but both seem sub-standard. I am seeking confirmation that this is as good as it gets, or that query improvements might be possible.

Answer 1

From your question, if your query parameters are optional from the stored procedure, you can try to use OR and AND let's judge your condition.

Other things

1. make sure you had added indexes for your query from your filter columns.
2. you might meet parameter-sniffing from your query which might let your query slow. so you can try to add OPTION(RECOMPILE)

query as below.

SELECT * 
FROM PatientTable
WHERE Active = @Active
AND (@PatientGender IS NULL OR PatientGender = @PatientGender)
AND (@PatientName = '' OR PatientName LIKE CONCAT('%',@PatientName,'%'))
OPTION(RECOMPILE)

NOTE

using OPTION(RECOMPILE) will tell QO not to try to keep the execution plan that might let your CPU become busy if your query is often query.