Hi,

I have a stored procedure (below) which looks for records matching 4
values.

The problem is that sometimes the @degreesubject, @degreegrade and
@alevel values may be 0 in which case I do not want the select query to
try and match them.

Can anyone explain the simplest way to write the SP using If/Else or
whatever? Do I have to write several If/Else's to cover each possible
combination of possible values?

Any help appreciated

Thanks,
Matt

CREATE PROCEDURE SearchUsersByJob

@jobid int,
@degreesubject int,
@degreegrade int,
@alevel int

as

select isnull(jobtitle,'') as jobtitle, isnull(location,'') as location
from vacancies where id = @jobid

and degreesubject = @degreesubject
and degreegradeid <= @degreegrade
and ucaspoints >= @alevel






*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

matt@smith.co.uk (matt@smith.co.uk) writes:
> The problem is that sometimes the @degreesubject, @degreegrade and
> @alevel values may be 0 in which case I do not want the select query to
> try and match them.

First, it may be better to use NULL as the value "don't care", in case
0 would be an actual value you would like to search for.

> Can anyone explain the simplest way to write the SP using If/Else or
> whatever? Do I have to write several If/Else's to cover each possible
> combination of possible values?

The simples is surely to write

and (degreesubject = @degreesubject OR @degreesubject = 0)

Note, though, that if you want SQL Server to use any index on degreesubject,
that you are better off to use IF ELSE and have different SELECT for
with and without degreesubject.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

>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.

Thanks anyway.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi

You could try

AND degreesubject = ISNULL(NULLIF(@degreesubject,0)),degreesubject)

or

AND degreesubject = CASE WHEN @degreesubject = 0 THEN degreesubject
ELSE @degreesubject
END

but they may not perform well!

John

<matt@smith.co.uk> wrote in message
news:40c88945$0$7863$c397aba@news.newsgroups.ws...
>
> >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.
>
> Thanks anyway.
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

matt@smith.co.uk (matt@smith.co.uk) writes:
>>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, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp