How to have conditional where clause based on parameter value

Question

I have following SQL,

DECLARE @EmployeeID Int

SELECT *
  FROM [Northwind].[dbo].[Orders]
  WHERE OrderID = 10248
  AND EmployeeID = @EmployeeID

I want to make sure IF @EmployeeID IS NULL Then do not include AND

Something like,

SELECT *
  FROM [Northwind].[dbo].[Orders]
  WHERE OrderID = 10248
  IF @EmployeeID IS NOT  NULL
  AND EmployeeID = @EmployeeID

I could think of creating a table variable and then filtering them based on parameter value , but is there a better way?

Gordon Linoff · Accepted Answer · 2017-10-04 00:41:55Z

9

I think you want:

WHERE OrderID = 10248 AND
      (@EmployeeId IS NULL OR EmployeeID = @EmployeeID)

answered Oct 4, 2017 at 0:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AB_87 · Accepted Answer · 2017-10-04 00:42:01Z

2

DECLARE @EmployeeID Int

SELECT *
  FROM [Northwind].[dbo].[Orders]
  WHERE OrderID = 10248
  AND EmployeeID = ISNULL(@EmployeeID, EmployeeID )

answered Oct 4, 2017 at 0:42

AB_87

1,1568 silver badges18 bronze badges