Isnull and is null in T-SQL

In the T-SQL, sometimes when determining whether a variable is null, someone may use the function isnull, such:

Declare   @ Var1   Varchar ( 20 )
Declare   @ Var2   Int
If   Isnull ( @ Var1 , '' ) =   ''   Print   ' @ Var1 is null. '
If   Isnull ( @ Var2 , - 1 ) =   - 1   Print   ' @ Var2 is null. '

This is actually a problem. Let's look at it again:

Declare   @ Var1   Varchar ( 20 )
Declare   @ Var2   Int
Set   @ Var1   =   ''
Set   @ Var2   =   - 1
If   Isnull ( @ Var1 , '' ) =   ''   Print   ' @ Var1 is null. '
If   Isnull ( @ Var2 , - 1 ) =   - 1   Print   ' @ Var2 is null. '

Obviously, @ var1 and @ var2 have been assigned values and are not null.

Let's look at more special cases:

Declare   @ Var3   Bit
Print   Isnull ( @ Var3 , - 1 )

The printed value is 1 rather than-1. This is because the @ var3 type is bit, and it can only have three values: 0, 1, or null, if values other than 0 and null are assigned to them (-1 here), they are regarded as 1.

The isnull function is used to determine whether the variable is null. the business logic in a specific context may run normally. For example, the above @ var1 will never be '', @ var2 will never be-1. There is also a case where @ var1 is used as an example. If the developer really wants the statement in the IF branch to be executed not only when @ var1 is null, and also executed when the value of @ var1 is an empty string.

To determine whether the variable is null, use is null:

Declare   @ Var1   Varchar ( 20 )
Declare   @ Var2   Int
Declare   @ Var3   Bit
Set   @ Var1   =   ''
Set   @ Var2   =   - 1
If   @ Var1   Is   Null   Print   ' @ Var1 is null. '   Else   Print   ' @ Var1 is not null. '
If   @ Var2   Is   Null   Print   ' @ Var1 is null. '   Else   Print   ' @ Var2 is not null. '
If   @ Var3   Is   Null   Print   ' @ Var1 is null. '   Else   Print   ' @ Var3 is not null. '

Is null is often used to form a logical expression appears in the WHERE clause of a single T-SQL statement, some people encounter control flow statements when writing the stored procedure but do not dare to use, but use isnull function, remember.