How to have conditional where clause based on parameter value

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?

2 Answers

I think you want:

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

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