search

[aish]

I have asp.net C# web application I want to search jobvacancy details using jobrole.
if user did not select any value I want to select all the details including null values.

this Jobvacancy table has JobRole feild and it allow to insert null values,

this is my stored procedure

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

Select NoOfVacancies,JobRole from JobVacancy where JobRole LIKE @JobRole + '%'

when I use @JobRole value as '%' then if JobRole feild has null value it did not select.
but I want to select all the values including null values. how can I do this?

[pty]

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

[aish]

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:

[pty]

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 :)

[aish]

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