>>The simples is surely to write
>>and (degreesubject = @degreesubject OR @degreesubject = 0)
> The problem with that is that it will match ALL users so there's no
> point including it. If @degreesubject = 0 then I don't want to match
> users using that criteria.
Huh? Does not that mean that you want to get back all users, that meets
the other criterias?
If you say:
select isnull(jobtitle,'') as jobtitle, isnull(location,'') as location
from vacancies where id = @jobid
and (degreesubject = @degreesubject or @degreesubject is null)
and (degreegradeid <= @degreegrade or @degreegrade is null)
and (ucaspoints >= @alevel or @alevel is null)
And then pass @jobid = 19 and @alevel = 12 and the other NULL, you will
get back all vacancies with id 19 and ucapoints >= 12, no matter the
value of degreesubject and degreegradeid. Is not that what you want?
If not, it may help if you post the following:
o CREATE TABLE statements for your table.
o INSERT statements with sample data.
o The desired output from the sample data.
This may help to iron out misunderstandings.
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server SP3 at