Checking an input param if not Null and using it i

问题:

What is the best way to include an input param in the WHERE clause but exclude it if it is null?

There are a number of ways I believe, but I can't seem to remember then.

Also could I use the COALESCE()? But I think this is only for SELECTing values?

Edit

To clarify, let's say a variable called @code ="1" then my where would be Where type='B' AND code = @code but if @code is null then I only want Where type='B' - notice the missing code = @code.

回答1:

You can use IsNull

 where some_column = IsNull(@yourvariable, 'valueifnull')

EDIT:

What you described in the comment can be done like:

where (@code is null or code = @code)

回答2:

How about

WHERE (Column1 = @Var1 OR @Var1 IS NULL)
AND (Column2 = @Var2 OR @Var2 IS NULL)

回答3:

Here's another approach

SELECT * FROM Thingies WHERE ( @thingId IS NULL OR ThingID = @thingId )

If your parameter is NULL, the rest of the OR expression won't be evaluated.

回答4:

check out this neat article here. It explains why "where (@param is null or Field=@param)" doesn't perform well and what to use instead.

回答5:

I’d like to suggest a solution which I found on another site:

SELECT * FROM Thingies 
WHERE ThingID = isnull(@ThingId,ThingID)

With this solution if the user selects null for your parameter then your query will return all the rows as the result.

回答6:

This question really helped me with a similar issue that had a few of us scratching our heads for a bit. I only write it up in case somebody else tries the same approach and cannot figure out why it does not work.

I was trying to only evaluate a part of a multipart WHERE clause if the @Parameter was not null. I tried to do this as below but always had no rows returned if @Parameter was null.

DECLARE @Parameter int = null;

SELECT  *  FROM  TABLE
WHERE   [AlternateID] is not null 
        AND (@Parameter is not null AND [AlternateID] = @Parameter)

I incorrectly thought that (@Parameter is not null AND [AlternateID] = @Parameter) would simply not form part of the full WHERE clause is @Parameter was null. However it was making the entire WHERE clause return false. The remedy was to add an OR 1=1 as below:

WHERE   [AlternateID] is not null 
        AND (@Parameter is not null AND [AlternateID] = @Parameter OR 1=1)

Of course the approach outlined by Ali (not enough reputation to upvote) solves this more efficiently.

WHERE   [AlternateID] is not null 
        AND [Partner_Customer_ID] = ISNULL(@Parameter, [Partner_Customer_ID])    

回答7:

You can use ISNULL(), or check for nulls explicitly as others have mentioned. This should be OK as long as you have no more than 1 or 2 optional input parameters. But if there are more parameters, this approach would be very inefficient as the indexes you create on those columns won't be used as you would expect. In such a case i would recommend you to use dynamic SQL. Here is an excellent article that explains why http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/