Vishal Khajuria wrote:

> Hi Fred,
> you can use this option also
> AND (IsNull(CLI_ID,0) = CASE WHEN @CLI_ID = 0 THEN IsNull(CLI_ID,0) ELSE
> @CLI_ID END
> )
> Thanks,
> Vishal Khajuria
>
> "Fred Morin" wrote:
>
>
>>THANKS A LOT.... WORKED THE FIRST TIME... Now I have about 100 Stored
>>Procedures to modify, but this is good news.. .Thanks
>>
>>Jonathan Chong wrote:
>>
>>
>>>Try this:
>>>
>>>AND (CLI_ID = CASE WHEN @CLI_ID = 0 THEN CLI_ID ELSE @CLI_ID END
>>>OR CLI_ID IS NULL
>>>)
>>>
>>>"Fred Morin" <fredmorin@hotmail.com> wrote in message
>>>news:erfUiUeoEHA.3424@TK2MSFTNGP12.phx.gbl...
>>>
>>>
>>>>I have a problem with a stored procedure
>>>>
>>>>SELECT CON_ID, CLI_ID, CON_LastName
>>>
>>>>FROM dbo.Contacts
>>>
>>>>WHERE (CON_ID = CASE WHEN @CON_ID = 0 THEN CON_ID ELSE @CON_ID END)
>>>>AND (CLI_ID = CASE WHEN @CLI_ID = 0 THEN CLI_ID ELSE @CLI_ID END)
>>>>
>>>>The problem I get is that it is possible for the table to have rows in
>>>>which CLI_ID is null....
>>>>
>>>>Those rows are not returned as of right now.
>>>>
>>>>How do I need to mofidy the
>>>>
>>>>CLI_ID = CASE WHEN @CLI_ID = 0 THEN CLI_ID ELSE @CLI_ID END
>>>>
>>>>Part so that when I do not specify a @CLI_ID parameter, all rows with
>>>>null CLI_ID will be returned (assuming the @CON_ID parameter would allow
>>>>it, since CON_ID is the key is can't be null, it's not a problem.)
>>>>
>>>>Thanks a lot.. I googled this everywhere but I am lost :(
>>>>
>>>>Fred
>>>
>>>
>>>