CODEWITHSUNDEEP
sqlsql-serverwhere-clauseresultset
shaneOverby
shaneOverby

Reputation: 60

SQL Server if bit parameter = 0 return records where field has value of 0 or null

I have a large stored procedure (500+ lines) from which Carrier records are returned. One of the parameters that I pass a value to when calling this stored procedure is to be used in a where clause to filter the result set. This is a bit type parameter, represented by a boolean property in my entity model (Web API).

I cannot figure out how to construct the where clause to return the appropriate records in the result set.

This is the basic outline of what I want to happen:

  1. If the bit type parameter is NULL return all records
  2. If the bit type parameter equals 1 return all records where the specified bit type field has a value of 1
  3. If the bit type parameter equals 0 return all records where the specified bit type field is null or has a value of 0

My attempts, out of context (please excuse, I'm not a strong SQL developer): 

DECLARE @Intrastate bit;
SET @Intrastate = 0; --should return all records where Intrastate field
                     --is null or has a value of 0
SELECT * FROM Carriers c
    WHERE @Intrastate IS NULL
        OR c.Intrastate = CASE WHEN @Intrastate = 1 THEN @Intrastate END
        OR c.Intrastate = CASE WHEN @Intrastate = 0 THEN @Intrastate END
        OR c.Intrastate = NULLIF(@Intrastate, 0)

The above query only returns records with Intrastate field having a value of 0, excludes records with Intrastate field as null.

DECLARE @Intrastate bit;
SET @Intrastate = 0; --should return all records where Intrastate field
                     --is null or has a value of 0
SELECT * FROM Carriers c
    WHERE @Intrastate IS NULL
        OR c.Intrastate = CASE WHEN @Intrastate = 1 THEN @Intrastate END
        OR c.Intrastate in (SELECT DISTINCT Intrastate FROM Carriers
                                WHERE Intrastate = 0
                                    OR Intrastate IS NULL)

Again, the above query only returns records with Intrastate field having a value of 0, excludes records with Intrastate as null.

Assigning NULL or 1 to the @Intrastate parameter produces the desired result sets, as defined above.

My issue is only with the result set return when the @Intrastate parameter has a value of 0.

Any and all help is highly appreciated.

Upvotes: 0

Views: 3516

Answers (3)

Ruslan Tolkachev
Ruslan Tolkachev

Reputation: 644

Ok, this solution looks like it is a lot of typing but if you have a function on the predicate column in the WHERE clause, sql server most likely going to scan the whole table and not going to use your indexes:

SELECT * FROM Carriers c
WHERE @Intrastate IS NULL
      OR (@Intrastate = 1 AND c.Intrastate = 1)
      OR ( @Intrastate = 0 AND (  c.Intrastate IS NULL 
                                  OR c.Intrastate = 0 ))

Upvotes: 0

J Sidhu
J Sidhu

Reputation: 677

This works if you SET ANSI_NULLS Off 

SET ANSI_NULLS Off
GO
WHERE Intrastate in (isnull(@Intrastate,Intrastate), nullif(isnull(@Intrastate,0), 0))

Upvotes: 1

CoderMarkus
CoderMarkus

Reputation: 1118

What about...

...
WHERE @Intrastate IS NULL
OR ISNULL(c.Intrastate, 0) = @Intrastate

That way if the variable is null, you get all records and if not, you coalesce the column to 0 if null and that should cover 1=1 and 0=0/null=0.

Upvotes: 4

Related Questions