SQL LIKE Find Query

Question

Hi All,

I have the following search sql query:

ALTER PROCEDURE [EmployeeSearch]
@PayrollNumber  varchar(50) = null,
@isInternal  bit = null,
@Firstname  varchar(50) = null,
@Surname  varchar(50) = null
AS
SET NOCOUNT ON;
Select EmployeeSearchView.*
From   EmployeeSearchView
Where
EmployeeSearchView.isInternal = @isInternal
AND 
(EmployeeSearchView.PayrollNumber LIKE '%' + @PayrollNumber + '%'
OR  EmployeeSearchView.Firstname LIKE '%' + @Firstname + '%'
OR  EmployeeSearchView.Surname LIKE '%' + @Surname + '%'

Only problem is when the FirstName or Surname is send through as an empty string '' it returns the wrong values...if i pass null values it works..how can I fix this query?

Thanks!

Answer 1

Could you add another and clause to check for empty strings?

ALTER PROCEDURE [EmployeeSearch]
@PayrollNumber  varchar(50) = null,
@isInternal             bit = null,
@Firstname              varchar(50) = null,
@Surname                varchar(50) = null
AS
SET NOCOUNT ON;
Select EmployeeSearchView.*
From   EmployeeSearchView
Where
EmployeeSearchView.isInternal = @isInternal
AND 
(EmployeeSearchView.PayrollNumber LIKE '%' + @PayrollNumber + ' %'
OR  (@Firstname <> '' AND EmployeeSearchView.Firstname LIKE '%' + @Firstname + ' %')
OR  (@Surname   <> '' AND EmployeeSearchView.Surname LIKE '%' + @Surname + ' %')

Answer 2

ALTER PROCEDURE [EmployeeSearch] @PayrollNumber varchar(50) = null, @isInternal bit = null, @Firstname varchar(50) = null, @Surname varchar(50) = null AS SET NOCOUNT ON;

IF (IsNull(@FirstName, '') = '') SET @FirstName = '%' ELSE SET @FirstName = '%' + @FirstName + '%'; END

IF (IsNull(@Surname, '') = '') SET @Surname = '%' ELSE SET @Surname = '%' + @Surname + '%'; END

Select EmployeeSearchView.* From EmployeeSearchView Where EmployeeSearchView.isInternal = @isInternal AND (EmployeeSearchView.PayrollNumber LIKE '%' + @PayrollNumber + '%' AND EmployeeSearchView.Firstname LIKE @Firstname AND EmployeeSearchView.Surname LIKE @Surname