sql-server - NULLIF 和 0 转换的 TSQL 问题

Question

我有一个小问题让我很伤心。在我看来合乎逻辑的事情对我不利。

我允许用户“批量更新”记录，并让他们能够更改一条或全部数据。

如果用户在totalLeaders中输入一个整数，比如1字段，它更新值就好了。

如果用户将该字段留空，它会将其转换为 null并跳过该更新，保留其当前值就好了。

但是，如果我传递 0在那个领域说没有领导者将接受培训，它说它是一个空值并且不执行 IF声明。

我希望 <totalLeaders>0</totalLeaders>不被视为空值并执行 IF陈述。我也期待 <totalLeaders></totalLeaders>被视为 NULL 值和 NOT执行 IF声明。

有什么想法吗？？

DECLARE @totalTrainingTime INT;
DECLARE @totalTeammates INT;
DECLARE @totalLeaders INT;
DECLARE @xml XML = '<root><data><totalLeaders>0</totalLeaders><totalTeammates>2</totalTeammates><totalTrainingTime>12</totalTrainingTime></data></root>';

-- Set our variables
SELECT @totalTrainingTime = NULLIF(ParamValues.x1.value('totalTrainingTime[1]', 'INT'), ''),
       @totalTeammates = NULLIF(ParamValues.x1.value('totalTeammates[1]', 'INT'), ''),
       @totalLeaders = NULLIF(ParamValues.x1.value('totalLeaders[1]', 'INT'), '')
FROM   @xml.nodes('/root/data') AS ParamValues(x1);

-- Test
SELECT @totalLeaders AS leaders,
       @totalTeammates AS teammates,
       @totalTrainingTime AS trainingTime

--Do we have leaders?
IF (@totalLeaders IS NOT NULL)
BEGIN
    SELECT 'Lets update the leaders because we were given a value'
END
ELSE
BEGIN
    SELECT 'We did not update leaders because it was a NULL value... However, it was really 0 that was passed'
END

--Do we have teammates?
IF (@totalTeammates IS NOT NULL)
BEGIN
    SELECT 'Lets update the teammates because we have a value'
END

Answer 1

试试这个:
NULLIF(ParamValues.x1.value('totalLeaders[1]/text()[1]', 'INT'),0)