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

It sounds like T.CONTRACTID is NULL in your data set. In that case, the ISNULL would be discarding the NULL parameter but then using the NULL field instead. In SQL, comparing NULL = NULL will act like FALSE (technically, comparing NULL = NULL will evaluate to NULL, which acts like FALSE when AND'ed with other conditions.) — Jordan Rieger, Jul 05 '19 at 23:29
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Jul 06 '19 at 04:16

score 3 · Accepted Answer · answered Jul 06 '19 at 00:11

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.

SQL Server : Optional Parameter Behavior Logic

1 Answers1