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?

Refer to https://stackoverflow.com/questions/87821/sql-if-clause-within-where-clause — Sunil Goli, Oct 04 '17 at 00:49

score 8 · Accepted Answer · answered Oct 04 '17 at 00:41

8

I think you want:

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

answered Oct 04 '17 at 00:41

Gordon Linoff

score 2 · Answer 2 · answered Oct 04 '17 at 00:42

2

DECLARE @EmployeeID Int

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

answered Oct 04 '17 at 00:42

AB_87

2 Answers2