CONDITIONAL 'WHERE'

THIS IS MY QUERY:
SELECT *
FROM users a
WHERE a.usertype = 'contact' AND ISNULL (a.FirstNm,'') LIKE 'MMColParam5%'
and ISNULL (a.LastNm,'') LIKE 'MMColParam6%'
AND STATUS IN (MMCOLPARAM7)
ORDER BY d.Attorney
THE LINE WHERE IT SAYS "AND STATUS IN (MMCOLPARM7), I WANT TO MAKE THAT
CONDITIONAL AND ONLY RUN IT IF MMCOLPARAM7 IS NOT NULL, IF IT IS NULL TO
SKIP THAT "AND" AND JUST EXECUTE THE REST OF THE QUERY.
HOW CAN THIS BE DONE ?
THANKS FOR THE HELP GUYS.
AHi
Try something like:
SELECT *
FROM users a
WHERE a.usertype = 'contact' AND ISNULL (a.FirstNm,'') LIKE 'MMColParam5%'
AND ISNULL (a.LastNm,'') LIKE 'MMColParam6%'
AND ( STATUS IN (MMCOLPARAM7)
OR MMCOLPARAM7 IS NULL )
John
"Aleks" wrote:

> THIS IS MY QUERY:
>
> SELECT *
> FROM users a
> WHERE a.usertype = 'contact' AND ISNULL (a.FirstNm,'') LIKE 'MMColParam5
%'
> and ISNULL (a.LastNm,'') LIKE 'MMColParam6%'
> AND STATUS IN (MMCOLPARAM7)
> ORDER BY d.Attorney
> --
> THE LINE WHERE IT SAYS "AND STATUS IN (MMCOLPARM7), I WANT TO MAKE THAT
> CONDITIONAL AND ONLY RUN IT IF MMCOLPARAM7 IS NOT NULL, IF IT IS NULL TO
> SKIP THAT "AND" AND JUST EXECUTE THE REST OF THE QUERY.
> HOW CAN THIS BE DONE ?
> THANKS FOR THE HELP GUYS.
> A
>
>|||With regards to

> AND STATUS IN (MMCOLPARAM7)
See if this works for status filtering
AND STATUS =coalesce(MMCOLPARAM7,STATUS)
HTH..
--
http://zulfiqar.typepad.com
BSEE, MCP
"Aleks" wrote:

> THIS IS MY QUERY:
>
> SELECT *
> FROM users a
> WHERE a.usertype = 'contact' AND ISNULL (a.FirstNm,'') LIKE 'MMColParam5
%'
> and ISNULL (a.LastNm,'') LIKE 'MMColParam6%'
> AND STATUS IN (MMCOLPARAM7)
> ORDER BY d.Attorney
> --
> THE LINE WHERE IT SAYS "AND STATUS IN (MMCOLPARM7), I WANT TO MAKE THAT
> CONDITIONAL AND ONLY RUN IT IF MMCOLPARAM7 IS NOT NULL, IF IT IS NULL TO
> SKIP THAT "AND" AND JUST EXECUTE THE REST OF THE QUERY.
> HOW CAN THIS BE DONE ?
> THANKS FOR THE HELP GUYS.
> A
>
>