Optional parameter on joined field which could be null?!

[Ole S. Pedersen]

I have two tables: eg. a person-table (no nulls allowed), with an id
and so on, and a person_course table (an intermediate table in a
many-to many relationship between person table and courses tables),
with two fields person_id and course_id.

But I want to make ONE multipurpose stored procedure, which has ONLY
optional parameters on all fields in the person table AND the field
course_id in the person_course table.

I have no problems making optional parameters on the person table (eg.
P.ID=ISNULL(@PersonID, P.ID ) ) BUT the problem is, when I try to add
an optional parameter on the field course_id it dosn't produce the
right results. Some times the course_id is null (because some persons
havn't joined a class yet).

Is there a way around it?

Ole S. Pedersen

[Simon Hayes]

os*@stofanet.dk (Ole S. Pedersen) wrote in message news:<c0**************************@posting.google. com>...

I have two tables: eg. a person-table (no nulls allowed), with an id
and so on, and a person_course table (an intermediate table in a
many-to many relationship between person table and courses tables),
with two fields person_id and course_id.

But I want to make ONE multipurpose stored procedure, which has ONLY
optional parameters on all fields in the person table AND the field
course_id in the person_course table.

I have no problems making optional parameters on the person table (eg.
P.ID=ISNULL(@PersonID, P.ID ) ) BUT the problem is, when I try to add
an optional parameter on the field course_id it dosn't produce the
right results. Some times the course_id is null (because some persons
havn't joined a class yet).

Is there a way around it?

Ole S. Pedersen

http://www.algonet.se/~sommar/dyn-search.html

Simon