This worked.

bm_GetClients @ClientKey = @ClientKey

It's going to be run from C#, and it should be ok there too. Thanks




>>>>This stored proc takes 2 optional params.
>>>>
>>>>

>>>>
>>>>CREATE PROCEDURE bm_GetClients
>>>>	@CompanyKey		INT = NULL,
>>>>	@ClientKey		INT = NULL
>>>>
>>>>AS
>>>>BEGIN
>>>>
>>>>	SELECT *
>>>>		FROM bm_Clients
>>>>		WHERE CompanyKey = ISNULL(@CompanyKey, CompanyKey) OR
>>>>			  ClientKey = ISNULL(@ClientKey, ClientKey)
>>>>
>>>>END
>>>>

>>>>
>>>>I want to pass either, both, or none. I'm running it like this:
>>>>
>>>>

>>>>DECLARE @CompanyKey	INT
>>>>DECLARE @ClientKey	INT
>>>>SET @CompanyKey = 2
>>>>SET @ClientKey = 5
>>>>
>>>>EXEC bm_GetClients
>>>>EXEC bm_GetClients @ClientKey
>>>>EXEC bm_GetClients @CompanyKey, @ClientKey 
>>>>EXEC bm_GetClients @CompanyKey
>>>>

>>>>
>>>>In all cases I'm getting back all records
>>>
>>>Change:
>>>

>>>WHERE CompanyKey = ISNULL(@CompanyKey, CompanyKey) OR
>>>      ClientKey  = ISNULL(@ClientKey, ClientKey)
>>>

>>>
>>>to
>>>

>>>WHERE CompanyKey = ISNULL(@CompanyKey, CompanyKey) AND
>>>      ClientKey  = ISNULL(@ClientKey, ClientKey)
>>>
>>>

>>
>>If I change the OR to AND, then the second run returns no records.
>
>
>Nope,
>It should because if the first parameter is NULL this:
>CompanyKey = ISNULL(@CompanyKey, CompanyKey) will be always TRUE
>
>BTW You should use Named parameters if you want to pass only second one or use NULL as first parameter:
>

>DECLARE @CompanyKey	INT
>DECLARE @ClientKey	INT
>SET @CompanyKey = 2
>SET @ClientKey = 5
>
>EXEC bm_GetClients @ClientKey = @ClientKey
>-- or
>EXEC bm_GetClients NULL, @ClientKey
>