"Not Me" <Not.Me@faker.fake.fa.kee> wrote in message
news:c974cs$e7l$1@ucsnew1.ncl.ac.uk...
> Hi, I can't seem to get this where clause to function as I wish.. must be
> something simple but I can't see it!
>
> have tried
> ac.coursename like CASE @course WHEN null THEN '%' ELSE '%' + @course +
'%'
> ac.coursename like CASE @course WHEN '' THEN '%' ELSE '%' + @course + '%'
> ac.coursename like CASE len(@course) WHEN 0 THEN '%' ELSE '%' + @course +
> '%'
>
> All work fine when I enter something in the parameter, but not when I
leave
> it blank! Any ideas why this is the ...case?

"Not Me" <Not.Me@faker.fake.fa.kee> wrote in message
news:c975lv$et0$1@ucsnew1.ncl.ac.uk...
> "Not Me" <Not.Me@faker.fake.fa.kee> wrote in message
> news:c974cs$e7l$1@ucsnew1.ncl.ac.uk...

"Not Me" <Not.Me@faker.fake.fa.kee> wrote in message
news:c974cs$e7l$1@ucsnew1.ncl.ac.uk...
> Hi, I can't seem to get this where clause to function as I wish.. must be
> something simple but I can't see it!
>
> have tried
> ac.coursename like CASE @course WHEN null THEN '%' ELSE '%' + @course +
'%'
> ac.coursename like CASE @course WHEN '' THEN '%' ELSE '%' + @course + '%'
> ac.coursename like CASE len(@course) WHEN 0 THEN '%' ELSE '%' + @course +
> '%'
>
> All work fine when I enter something in the parameter, but not when I
leave
> it blank! Any ideas why this is the ...case?
>
> Cheers!
> Chris
>
>

"tperovic" <tonyperovic@yahoo.com> wrote in message
news:pnHtc.28618$zO3.1966@newsread2.news.atl.earthlink.net...
> "Not Me" <Not.Me@faker.fake.fa.kee> wrote in message
> news:c974cs$e7l$1@ucsnew1.ncl.ac.uk...
> > ac.coursename like CASE @course WHEN null THEN '%' ELSE '%' + @course +
> '%'
> > ac.coursename like CASE @course WHEN '' THEN '%' ELSE '%' + @course +
'%'
> > ac.coursename like CASE len(@course) WHEN 0 THEN '%' ELSE '%' + @course
+
> > '%'
> >
> > All work fine when I enter something in the parameter, but not when I
> leave it blank!

> I've been using this:
>
> ac.coursename = COALESCE(@course,ac.coursename)
>
> When @course is null then the column is compared to itself which is always
> true.

> Along the same lines as above, i.e. wanting to include all rows if no
> criteria specified - What can I do in the case of integers? what would be
> the %/catch-all for this?

> Is there a better way alltogether of using 'optional' parameters?

"Erland Sommarskog" <sommar@algonet.se> wrote in message
news:Xns94F929C429C8Yazorman@127.0.0.1...
> Not Me (Not.Me@faker.fake.fa.kee) writes:
> > Along the same lines as above, i.e. wanting to include all rows if no
> > criteria specified - What can I do in the case of integers? what would
be
> > the %/catch-all for this?
>
> Depends on your business domain, but NULL would certainly be the
> best choice.
>
> > Is there a better way alltogether of using 'optional' parameters?
>
> Don't really know what you are into, but
> http://www.sommarskog.se/dyn-search.html might be something for you.