How to write Stored Procedure to get employee details of one department or all department

Imagine there is a table called Employee. And you have interface like below then you must write query for both single department and all department option,

 Then you can write stored procedure without using if conditions as follows.

CREATE PROCEDURE sp_Get_Edetails

 @DepartmentId int

AS

BEGIN

 SET NOCOUNT ON;

 SELECT * FROM Employee

 WHERE Employee.DepartmentId = @DepartmentId

 OR @DepartmentId = 0;

END

When department id is 1

When department id is 0 (All option)

According to the above example, query return value if '@DepartmentId' has value or it set to zero. '@DepartmentId' set zero means nothing to check as department, and then it is all option.