[Go] isnull and is NULL in T-SQL

In T-SQL, sometimes it is possible to determine if a variable is null, and someone might use the function isnull, such as:

1 DECLARE @VAR1 VARCHAR (2DECLARE @VAR2 INT3" ) "@VAR1 is null. ' 4 IF ISNULL (@VAR2,-1) =-1'@VAR2 is null. '

This usage is actually problematic, and then look at:

1DECLARE @VAR1 VARCHAR ( -)2 DECLARE @VAR2 INT3SET @VAR1 ="'4SET @VAR2 =-15IF ISNULL (@VAR1,"') ="'PRINT'@VAR1 is null.'6IF ISNULL (@VAR2,-1) = -1PRINT'@VAR2 is null.'

It is clear that both @VAR1 and @var2 are assigned and are NOT NULL.

Look at the more special situation:

1 DECLARE @VAR3 BIT 2 PRINT ISNULL (@VAR3,-1)

The printed value is 1, not 1, because the type of the @VAR3 is bit, with a possible value of only three: 0, 1, or null, and if it assigns a value other than 0 and null (here-1), it is considered 1.

The IsNull function is used to determine if a variable is null, which may be normal in the business logic of a particular context, as the above @var1 will never be ", @VAR2 will never be 1; and in the case of @var1, for example, If the developer does want the statement in the IF branch to execute not only when @var1 is null, but also when the value of @var1 is an empty string ("').

The determination of whether a variable is null should use is null:

1DECLARE @VAR1 VARCHAR ( -)2 DECLARE @VAR2 INT3 DECLARE @VAR3 BIT4SET @VAR1 ="'5SET @VAR2 =-16IF @VAR1 is NULL PRINT'@VAR1 is null.'ELSE PRINT'@VAR1 is not null.'7IF @VAR2 is NULL PRINT'@VAR1 is null.'ELSE PRINT'@VAR2 is not null.'8IF @VAR3 is NULL PRINT'@VAR1 is null.'ELSE PRINT'@VAR3 is not null.'

IS-null is often used to compose a logical expression that appears in a WHERE clause of a single T-SQL statement, and some people encounter a control flow statement while writing a stored procedure but are afraid to use the IsNull function instead.

[Go] isnull and is NULL in T-SQL