Overview of NULL data in SQL Server

In SQL Server, when you compare functions or data functions, null may have some problems, which can lead to unpredictable results.

First, let's talk about null and mathematics.

1 If null appears in any SQL mathematics, the result is always null.

The following shows an instance.

 

Query the data of the three tables, and the card number is required to be userid = 2, or not in the checkout status, and add the obtained data.

 

The following is the transaction code in the stored procedure.

 

Alterprocedure [DBO]. [proc_checkbill]

-- Add the parameters for the stored procedure here

@ Useridvarchar (15)

As

Begin

-- Set nocount on added to prevent extra result sets from

-- Interfering with select statements.

Setnocounton;

Begintran -- start transaction

Selectcount (cardid) as 'Card sale number' fromt _ studentinfowhereuserid = @ useridandidentify = 'uncheck'

Selectcount (cardid) as 'number of logins', coalesce (sum (cancelcash), 0) as 'amount of login' fromt _ cancelwhereuserid = @ useridandstatue = 'login'

Selectcoalesce (sum (addmoney), 0) as 'Recharge account' fromt _ rechargewhere
Userid = @ useridandstatue = 'uncheck'

 

Updatet_studentinfo setidentify = 'checkout 'whereuserid = @ useridandidentify = 'unsettled'

Updatet_cancelsetstatue
= 'Checked out' whereuserid = @ useridandstatue = 'unsettled'

Updatet_rechargesetstatue
= 'Checked out' whereuserid = @ useridandstatue = 'unsettled'

Declare @ regionerrorint

Select @ regionerror = @ error -- check every execution of a T-SQL statement and save the error code to a local variable.

If
(@ Regionerror
= 0) -- determines if both statements are successfully executed

Committran -- execute a transaction

Else

Rollbacktran -- roll back a transaction

End

 

Imagine that we can query the recharge amount. If the data we find is0
Or a specific number, Then the returned result after it is added should also be0 or a specific numberOnly

What about.

The actual result is not what we want.

 

 

 

After the storage is overcharged

 

 

We can see that the return value of the recharge amount after execution is null, and this value cannot be added in mathematics.

This is the unknown data represented by null, rather than any specific value.

 

Therefore, we should pay special attention to the processing of null, especially in mathematics. Make sure the returned valueNoNull. To solve this problem, you can select to filter NULL results. Or use a function to change the null value to something that the end user can understand.

 

Here, the coalesce () function plays a very good role.

 

Coalesce
: Return the first non-empty expression in the parameter.

Syntax

Coalesce (expression [,... n])

Parameters

Expression

Any type of expression.

N

It indicates that placeholders of multiple expressions can be specified. All expressions must be of the same type, or can be implicitly converted to the same type.

 

Return type

Returns the same value as expression.

Note

If all the independent variables are null, coalesce returns
Null Value

 

Therefore, the above error modification method is to add this function before the sum.

Select coalesce (sum (addmoney), 0) as 'Recharge payby' from t_recharge where userid = @ userid and statue
= 'Unsettled Check'

 

 

 

The problem is solved here.In the next blog, I will continue to describe the usage of null, and then describe null.