Create Database Stored Procedures to search fields in any combination (MS SQL)

Consider a table tblStaff as follows

id	name	age	departmentID
1	Prasanth	30	1
2	Martin	25	5
3	Abdul	30	7
4	Kiran	30	1

We can write stored procedues to get the details according to any of the fields or a combination of the fields.

But the problem occurs when some of the values should not be considered.
For eg: you have a stored procedure to search for ID and departmentID.

CREATE PROCEDURE usp_getStaff
    @id int = 0,
    @departmentid int = 0
AS
BEGIN
    SET NOCOUNT ON;
SELECT
    *
FROM
    tblStaff
WHERE
    id = @id
AND
    departmentID = @departmentid

Then, you have to pass both the parameters.

exec usp_getStaff 1,1

That is, you cannot use this SP to search either id or departmentID. You will need different SPs to search ID and departmentID independently.
Now, I will try to explain a method using which you can write SP that can be used to search fields in any combination.

Consider the modified SP below

CREATE PROCEDURE usp_getStaff
    @id int = 0,
    @name varchar(50) = '',
    @age int = 0,
    @departmentid int = 0
AS
BEGIN
    SET NOCOUNT ON;
SELECT
    *
FROM
    tblStaff
WHERE
    (@id = 0 OR id = @id)
AND
    (@name = '' OR name = @name)
AND
    (@age = 0 OR age = @age)
AND
    (@departmentid = 0 OR departmentid = @departmentid)

END
GO

This SP requires all the four parameters to be passed, but you can specify which all values should be considered by passing the default values.

eg:
    exec usp_getStaff 0,'',30,0

will return all the records with age = 30

1    Prasanth    30    1
3    Abdul        30    7
4    Kiran         30    1

similarly,
    exec usp_getStaff 0,'',30,1

will return all records with age = 30 and departmentid = 1
1    Prasanth    30    1
4    Kiran         30    1

Here, we have modified the WHERE condition. We are grouping conditions for each fields such that if we pass the default value, the condition for the field is discarded.
eg: we are passing '0' for ID. In the WHERE condition, we are first checking whether the passed value is '0'. And, if the value is '0', the first part of the 'OR' condition is success and it does not consider the second part where the actual checking with DB value is taking place.

I would like to hear from you if this really helped and saved your day.