Search ID, first name and last name in SQL using Like

Question

Hi I have this stored procedure for Searching employee information. I want the user to have the choice to enter ID, or First Name or Last Name. but when I execute this stored procedure, it requires the other parameters.

  create proc searchtry
  @empID int,
  @firstname varchar(20),
  @lastname varchar(20)
  as
  begin
  select fld_EmployeeId,fld_EmployeeFirstName,fld_EmployeeLastName
  from Reference.tbl_EmployeeDetails
  where fld_EmployeeId  like @empID
  OR fld_EmployeeFirstName  like @firstname
  OR fld_EmployeeLastName  like @lastname
  end

possible duplicate of [How can I use optional parameters in a T-SQL stored procedure?](http://stackoverflow.com/questions/3415582/how-can-i-use-optional-parameters-in-a-t-sql-stored-procedure) — bendataclear, Jul 30 '13 at 10:52
whenever youre using stored proc you should supply all parameters you declared — zxc, Jul 30 '13 at 10:52

score 0 · Answer 1 · answered Jul 30 '13 at 10:52

You should give default values to the parameters

create proc searchtry
  @empID int = NULL,
  @firstname varchar(20) = '',
  @lastname varchar(20) = ''
  as
  begin
  select fld_EmployeeId,fld_EmployeeFirstName,fld_EmployeeLastName
  from Reference.tbl_EmployeeDetails
  where fld_EmployeeId  like @empID
  OR fld_EmployeeFirstName  like @firstname
  OR fld_EmployeeLastName  like @lastname
  end

Search ID, first name and last name in SQL using Like

1 Answers1