CREATE PROCEDURE [dbo].[USP_getEmployeeList]
(
@DeptId INT=NULL,
@OfficeId INT=NULL
)
AS
BEGIN
IF(@DeptId IS NOT NULL AND @OfficeId IS NOT NULL)
BEGIN
SELECT *
FROM dbo.Employees
WHERE DeptId=@DeptId AND OfficeId=@OfficeId
END
ELSE IF(@DeptId IS NOT NULL AND @OfficeId IS NULL)
BEGIN
SELECT *
FROM dbo.Employees
WHERE DeptId=@DeptId
END
ELSE IF(@DeptId IS NULL AND @OfficeId IS NOT NULL)
BEGIN
SELECT *
FROM dbo.Employees
WHERE OfficeId=@OfficeId
END
ELSE
BEGIN
SELECT *
FROM dbo.Employees
END
END
OR
CREATE PROCEDURE [dbo].[USP_getEmployeeList]
(
@DeptId INT=NULL,
@OfficeId INT=NULL
)
AS
BEGIN
SELECT *
FROM dbo.Employees
WHERE (@DeptId IS NULL OR DeptId=@DeptId)
AND (@OfficeId IS NULL OR OfficeId=@OfficeId)
Note:- Just add conditions like these two to the WHERE part, as many as optional parameters you have in your stored procedure.
END
