Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
0 like 0 dislike
244 views
in SQL Queries by 13 20 26

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?


1 Answer

0 like 0 dislike
by 11 25 38

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

If you don’t ask, the answer is always NO!
...