CODEWITHSUNDEEP
sqlif-statementnullwhere-clause
ernest
ernest

Reputation: 1724

IF Inside WHERE with stored procedure

I have a WHERE statement that looks like this:

WHERE 
    ((@Value1 IS NULL AND [value1_id] IS NULL) OR [value1_id] = ISNULL(@Value1, [value1_id]))
    AND
    ((@Value2 IS NULL AND [value2_id] IS NULL) OR [value2_id] = ISNULL(@Value2, [value2_id]))
    AND
    ((@Value3 IS NULL AND [value3_id] IS NULL) OR [value3_id] = ISNULL(@Value3, [value3_id]))
    AND
    ((@Value4 IS NULL AND [value4_id] IS NULL) OR [value4_id] = ISNULL(@Value4, [value4_id]))
    AND
    ((@Value5 IS NULL AND [value5_id] IS NULL) OR [value5_id] = ISNULL(@Value5, [value5_id]))
    AND 
    ((@Value6 IS NULL AND [value6_id] IS NULL) OR [value6_id] = ISNULL(@Value5, [value6_id]))

I need to add some conditional logic inside of the WHERE so I can do special things with Value5 and Value6. Basically, if Value5, Value6, or another value is null, I want to use the Value 5 and Value 6 lines as is. If all three values aren't NULL, I need to run some calculations on the values.

Any ideas on what the best action would be?

Upvotes: 2

Views: 464

Answers (2)

Josien
Josien

Reputation: 13867

Basically, if Value5, Value6, or another value is null, I want to use the Value 5 and Value 6 lines as is. If all three values aren't NULL, I need to run some calculations on the values.

I think this will be hard to do with for example a CASE inside the WHERE clause. You might be better off with an IF construction outside of your SELECT statements:

IF (@Value5 IS NULL OR @Value6 IS NULL OR @OtherValue IS NULL)
  BEGIN
    <Statement1AsIs>
  END
ELSE
  BEGIN
    <Statement2WithComputations>
  END

Rewrite WHERE clause

On a short sidenote, I know you did not ask for it, but I have the feeling there must be a 'clearer' way to write these

((@Value1 IS NULL AND [value1_id] IS NULL) OR [value1_id] = ISNULL(@Value1, [value1_id]))

I've been playing around a bit and this is one way to say the same:

COALESCE(@Value1, [value1_id], 1001) = COALESCE([value1_id], 1001)

This is another one, equivalent and shorter:

@Value1 IS NULL OR [value1_id] = ISNULL(@Value1, [value1_id])

Check out this SQL Fiddle to see the equivalence.

Upvotes: 1

jdl
jdl

Reputation: 1104

Use CASE

"CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING."

http://msdn.microsoft.com/en-us/library/ms181765.aspx

You can perform calculations inside of a SELECT like this:

SELECT
    CASE
        WHEN @Value7 is not null THEN @Value7 * 100
        ELSE Value7 * 100
    END
FROM #T
WHERE   (@Value1 is null and Value1 is null)
    AND (@Value2 is null and Value2 is null)

It's still not very clear what exactly you're trying to accomplish. Is the WHERE not inside a SELECT? Provide a clear, concise example if the above is not correct.

Upvotes: 4

Related Questions