Dynamic sql question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic sql question

Author

Topic

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2002-04-18 : 14:17:12

Hey SQL connoisseurs,

I have a relatively basic proc.

I have 3 parameters passed in (all optional (NULL if empty)).

I have a select with some outer joins.

then a where clause.
The issue is this.
if any of the 3 vars is passed in (Not NULL), I want to query on that, if it isn't passed I want to ignore it as part of the where.

This does not seem to work.
It will only return results where the columns are NOT NULL
pj.bookingid = NULL and @bookingid = NULL (but i guess this isn't equal) .
HOw do i make this query? any ideas?

WHERE
pj.phototype = Coalesce(@phototype,pj.phototype)
and
pj.offenderid = Coalesce(@offenderid,pj.offenderid)
and
pj.bookingid = Coalesce(@bookingid,pj.bookingid)

_________________________
Death is one day closer

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-04-18 : 14:19:21


select *
  from table_a
       left outer join table_b on table_a.id = table_b.ref_id
 where (table_a.column = @parameter_1
    or @parameter_1 is null)
   and (table_b.column = @parameter_1
    or @parameter_1 is null)
   and (table_a.id = @parameter_2
    or @parameter_2 is null)

or for your where statement


where (pj.phototype = @phototype
   or @phototype is null)
  and (pj.offenderid = @offenderid
   or @offenderid is null)
  and (pj.bookingid = @bookingid
   or @bookingid is null)

Edited by - onamuji on 04/18/2002 14:21:56

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2002-04-18 : 14:40:10

doh!

Thanks

I had it written that way with a small change
i was saying
(pj.bookingid = @bookingid
or pj.bookingid is null)

Thanks for the help.

_________________________
Death is one day closer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources