CODEWITHSUNDEEP
sql-serversql-server-2008
user998026
user998026

Reputation:

Is SQL Server's double checking needed here?

IF @insertedValue IS NOT NULL AND @insertedValue >  0

This logic is in a trigger.

The value comes from a deleted or inserted row (doesn't matter).

2 questions :

  1. Do I need to check both conditions? (I want all value > 0, value in db can be nullable)

  2. Does SQL Server check the expression in the order I wrote it ?

Upvotes: 0

Views: 81

Answers (2)

David M
David M

Reputation: 72880

1) Actually, no, since if the @insertedValue is NULL, the expression @insertedValue > 0 will evaulate to false. (Actually, as Martin Smith points out in his comment, it will evaluate to a special value "unknown", which when forced to a Boolean result on its own collapses to false - examples: unknown AND true = unknown which is forced to false, unknown OR true = true.) But you're relying on comparison behaviour with NULL values. A single step equivalent method, BTW, would be:

IF ISNULL(@insertedValue, 0) > 0

IMHO, you're better sticking with the explicit NULL check for clarity if nothing else.

2) Since the query will be optimised before execution, there is absolutely no guarantee of order of execution or short circuiting of the AND operator.

Combining the two - if the double check is truly unnecessary, then it will probably be optimised out before execution anyway, but your SQL code will be more maintainable in my view if you make this explicit.

Upvotes: 5

Pankaj
Pankaj

Reputation: 10105

You can use COALESCE => Returns the first nonnull expression among its arguments.

Now you can make the query more flexible, by increasing the column limits and again you need to check the Greater Then Zero condition. Important point to note down here is you have the option to check values in multiple columns.

declare @val int
set @val = COALESCE( null, 1, 10 )
if(@val>0)
    select 'fine'
else
    select 'not fine'

Upvotes: 0

Related Questions