Optional parameter in Stored procedure

Question

I created this stored procedure

create proc SelectEmployee

@Name nvarchar(30),
@Gender nvarchar(30)
as
begin
    Select * from Employee where name=@Name and gender = @Gender
end

but when I execute without a parameter I get this error 

Procedure or function 'SelectEmployee' expects parameter '@Name', which was not supplied.

Is it possible to execute a stored procedure without providing the parameter value? and How can I make the parameter not mandatory in a stored procedure?

Answer 1

parameter is considered optional in Stored procedure if the parameter has a default value specified when it is declared.
So in this case you will Alter Stored procedure and set @Name,@Gender parameter to Null
Here is the Stored procedure after changing:

Alter proc SelectEmployee

@Name nvarchar(30)=NULL ,
@Gender nvarchar(30)=NULL
as
begin

Select * from Employee where name=@Name and gender = @Gender

end

See also Specify Parameters