Select all the record from Database Table if parameter is null in SQL Server

Hi Mahesh1986,

As per your query i have create the sample. If you have any doubt please revert me back. Below is the given sample.

SQL 

CREATE PROCEDURE Employee_GetDetailsBasedonGender
		@Gender VARCHAR(10) = NULL
AS
BEGIN

	DECLARE @Employee TABLE
	(
		Eid INT
		,Name VARCHAR(200)
		,Gender CHAR(10)
	)

	INSERT INTO @Employee VALUES(1,'Alpesh','Male')
	INSERT INTO @Employee VALUES(2,'Devang','Male')
	INSERT INTO @Employee VALUES(3,'Pankaj','Male')
	INSERT INTO @Employee VALUES(4,'Saheen','Female')
	INSERT INTO @Employee VALUES(6,'Ajay','Male')
	INSERT INTO @Employee VALUES(7,'Sakshi','Female')
	INSERT INTO @Employee VALUES(8,'Ajay','Male')
	INSERT INTO @Employee VALUES(9,'ifra','female')

	SELECT * 
	FROM  @Employee
	WHERE Gender = @Gender
	OR @Gender IS NULL
END

Output

EXEC Employee_GetDetailsBasedonGender NULL

EXEC Employee_GetDetailsBasedonGender 'Male'

EXEC Employee_GetDetailsBasedonGender 'Female'

CREATE PROCEDURE Employee_GetDetailsBasedonGender
@Eid int=0,
@Name nvarchar =null,
@Gender int=0,
@status varchar(max)
As
Begin

if @status='gender_selected'
begin
SELECT * FROM  Employee WHERE Gender = @Gender
end

if @status='all_records'
begin
SELECT * FROM  Employee
end
end