CODEWITHSUNDEEP
sql-servert-sqlstored-proceduressql-server-2012
SBB
SBB

Reputation: 8990

TSQL Issue with NULLIF and 0 conversion

I am having a small issue that is causing me some grief. Something that seems logical in my head isn't working in my favor.

I allow users to "Mass Update" records and give them the ability to change one piece of data or all of it.

If the user enters a integer such as 1 in the totalLeaders field, it updates the value just fine.

If the user leaves the field empty, it converts it to null and skips that update leaving its current value just fine.

However, if I pass a 0 in that field to say that no leaders will be trained, it says its a null value and doesn't execute the IF statement.

I would expect <totalLeaders>0</totalLeaders> to not be treated as a null value and execute the IF statement. I would also expect <totalLeaders></totalLeaders> to be treated as a NULL value and NOT execute the IF statement.

Any thoughts??

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

Upvotes: 3

Views: 95

Answers (3)

sqluser
sqluser

Reputation: 5672

Get it as VARCHAR and then CONVERT it if needed

@totalLeaders = NULLIF(ParamValues.x1.value('totalLeaders[1]', 'varchar(50)'), '')

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

I am not entirely certain why the value zero be appearing as NULL, but one option might be to convert to VARCHAR first:

@totalLeaders = CONVERT(INT, NULLIF(ParamValues.TaskChainerTask.query('Property1').value('totalLeaders[1]', 'VARCHAR(5)'),''))

Please have a look at this SO link for more information.

Upvotes: 0

Amit Sukralia
Amit Sukralia

Reputation: 950

Try this:
NULLIF(ParamValues.x1.value('totalLeaders[1]/text()[1]', 'INT'),0)

Upvotes: 2

Related Questions