SQL Server : Optional Parameter Behavior Logic

Question

I am implementing a stored procedure in SQL Server 2014, with two parameters: @CLIENTID and @CONTRACTID. One of the parameters is optional, so, when the stored procedure receives only the Client ID, it should return all the information related to that client, and when it receives both Client ID and Contract ID, it should return only the information related to that particular contract, from that particular client.

Here's some example code...

CREATE PROCEDURE SP_EXAMPLE_STACKOVERFLOW
    @CLIENTID INT,
    @CONTRACTID INT = NULL
AS
    SELECT
        *
    FROM 
        Table T
    WHERE 
        T.CLIENTID = @CLIENTID
        AND (T.CONTRACTID = @CONTRACTID OR ISNULL(@CONTRACTID, 0) = 0)

The code above works, however my first attempt was to make the last line like this:

AND T.CONTRACTID = ISNULL(@CONTRACTID, T.CONTRACTID)

However this didn't work... It basically considered this last line to be evaluated to FALSE all the time.

I can't figure out why... And I'd appreciate some help

Answer 1

I think you want:

SELECT T.*
FROM Table T
WHERE T.CLIENTID = @CLIENTID AND
      (@CONTRACTID IS NULL OR T.CONTRACTID = @CONTRACTID)

This will return all contract for a client if @CONTRACTID is NULL. It will return only the specified contract, if it is not NULL.

The fact that this doesn't work as expected:

T.CONTRACTID = ISNULL(@CONTRACTID, T.CONTRACTID)

suggests that T.CONTRACTID can be NULL. That is the only value that would not be equal to itself.