create table Customers(
   CusID int not null,
   Name varchar(50) null,
   Surname varchar(50) null,
   CusNo int not null,
   Deleted bit not null
)


create proc spCusLs (
   @CusID int = null,
   @Name varchar(50) = null,
   @Surname varchar(50) = null,
   @CusNo int = null
)
as

select 
   CusID,
   Name,
   Surname,
   CusNo
from
   Customers
where
   Deleted = 0
   and CusID <> 1000
   and (@CusID is null or CusID = @CusID)
   and (@CusNo is null or CusNo = @CusNo)
   and (@Name is null or Name like @Name)
   and (@Surname is null or Surname like @Surname)
order by
   Name,
   Surname


create nonclustered index ix_customers_name on customers ([name] asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary

create nonclustered index ix_customers_surname on customers (surname asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary

create nonclustered index ix_customers_cusno on customers (cusno asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary