search

select
NoOfVacancies,
JobRole
from JobVacancy
where (JobRole like @JobRole + '%')
or (JobRole is null)

Thanks for your help using your ideas finally I manage to write a correct query,

CREATE PROCEDURE JobVacancy
(
@JobRole varchar (50)
)
as

if @JobRole <> '%'
begin 
Select NoOfVacancies,JobRole from JobVacancy where JobRole LIKE @JobRole+ '%' 
end
else
begin 
Select NoOfVacancies,JobRole from JobVacancy where (JobRole LIKE @JobRole+ '%' OR JobRole is null)
end

:evil:

no problem, but with your solution i don't understand why you use an if..else statement here

if @JobRole <> '%'
begin 
Select NoOfVacancies,JobRole from JobVacancy where JobRole LIKE @JobRole+ '%' 
end
else
begin 
Select NoOfVacancies,JobRole from JobVacancy where (JobRole LIKE @JobRole+ '%' OR JobRole is null)
end

im sure you'd get the same result by just saying

select NoOfVacancies, JobRole
from JobVacancy
where JobRole like @JobRole + '%'
or JobRole is null

if @JobRole is '%' your statement would say '..where JobRole like '%%' which is the same as saying where JobRole like '%'; both solutions would work but I think you may be overcomplicating slightly.

on the right track though :)

your correct I can get the result with out using if else. but then for ex. if I want to select 'Programmer' then JobRole=='programmer' and JobRole=='null' selected but I want to select JobRole=='null' only when I put JobRole=='%' otherwise I don't want to select null values.
:o