CODEWITHSUNDEEP
sqlsql-server
BruceyBandit
BruceyBandit

Reputation: 4324

How to change = 0 to IS NULL

Below I am using OUTPUT Parameters to output an update statement:

    UPDATE set Value = src.Value, DeletedDate = iif (@Deleted =1, getutcdate(), NULL), ModifiedBy = SUSER_SNAME(), ModifiedDate = getutcdate()
WHEN NOT MATCHED THEN
    INSERT (ConfigKey, Value, AddedBy, ModifiedBy)
    VALUES (@ConfigKey, @Value, SUSER_SNAME(), SUSER_SNAME())
OUTPUT 'Core.Config', 'ConfigID', inserted.ConfigID,
    iif($action = 'Update',  Core.updXMLFragment('Value', inserted.Value, deleted.Value)+
                             Core.updXMLFragment('ModifiedBy', inserted.ModifiedBy, deleted.ModifiedBy) +                                   
                             Core.updXMLFragment('DeletedDate', inserted.DeletedDate, deleted.DeletedDate)

                          ,  Core.insXMLFragment('ConfigID') + 
                             Core.addnlXMLFragment ('ModifiedBy', inserted.ModifiedBy))

The code above generates the update statement at the bottom, but the issue I have is 'DeletedDate = 0' in the update statement. I need this to be changed to DeletedDate=NULL and in the condition 'DeletedDate IS NULL'. How can this be done?

BEGIN TRAN
update Core.Config
                set Value = 'Insert',ModifiedBy = 'HARROGATE\Name' ,DeletedDate = '0'
                where ConfigID = '1' and Value = 'Update' and ModifiedBy = 'HARROGATE\Name' and DeletedDate = '0'
                if @@ROWCOUNT <> 1 
                    BEGIN;
                        THROW 99999, 'Error - Single Row not updated, rollback terminated', 1;
                    END;

UPDATE: Included updxmlfragment

BEGIN


if (@InsertedValue = '')
BEGIN
set @InsertedValue = ' ' 
END
if(@DeletedValue = '')
BEGIN
set @DeletedValue = ' '
END

if(@FieldName IS NULL)
--if(@InsertedValue IS NULL and @DeletedValue IS NULL or @FieldName IS NULL) -- pre v2.1 logic
BEGIN

-- This "errors" the function and ensures the script doesn't get any further than the update
return convert(int, 'NULL Params passed for XML Fragment Generation')

END

-- Validate the date has the correct "time" element associated - i.e HH:MM:SS:MSS
if (@FieldName like '%date%')
BEGIN

if((TRY_PARSE(@InsertedValue as datetime) IS NOT NULL) or (TRY_PARSE(@DeletedValue as datetime) IS NOT NULL))
BEGIN
if((len(replace(@DeletedValue, ':', '')) +3 <> len(@deletedvalue)) OR (len(replace(@InsertedValue, ':', '')) +3 <> len(@InsertedValue)))
BEGIN
-- This "errors" the function and ensures the script doesn't get any further than the update
return convert(datetime, 'Invalid Date Params passed for XML Fragment Generation')
END
END
END

return 
'<Update><FieldName>'+@FieldName+'</FieldName>'+iif(@DeletedValue is null, '<OldValue />',  '<OldValue><![CDATA['+@DeletedValue+']]></OldValue>') 
+iif(@InsertedValue is null, '<NewValue />',  '<NewValue><![CDATA['+@InsertedValue+']]></NewValue>') + '</Update>

'

Upvotes: 0

Views: 113

Answers (1)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

Why not to use NULLIF()?

Returns a null value if the two specified expressions are equal.

So you'd have to use NULLIF(DeletedDate, 0) instead of DeletedDate only.

Update

As per my understanding, you have to use it like that:

UPDATE
SET Value = src.Value
  , DeletedDate = IIF(@Deleted = 1, getutcdate(), NULL)
  , ModifiedBy = SUSER_SNAME()
  , ModifiedDate = getutcdate() WHEN NOT MATCHED THEN
INSERT (ConfigKey, Value, AddedBy, ModifiedBy)
VALUES (@ConfigKey, @Value, SUSER_SNAME(), SUSER_SNAME())
OUTPUT 'Core.Config'
  , 'ConfigID'
  , inserted.ConfigID
  , IIF($ACTION = 'Update', Core.updXMLFragment('Value', inserted.Value, deleted.Value) + Core.updXMLFragment('ModifiedBy', inserted.ModifiedBy, deleted.ModifiedBy) + Core.updXMLFragment('DeletedDate', NULLIF(inserted.DeletedDate, 0), NULLIF(deleted.DeletedDate, 0))
  , Core.insXMLFragment('ConfigID') + Core.addnlXMLFragment('ModifiedBy', inserted.ModifiedBy))

UPDATE Core.Config
SET Value = 'Insert'
  , ModifiedBy = 'HARROGATE\Name'
  , DeletedDate = NULL
WHERE ConfigID = '1'
  AND Value = 'Update'
  AND ModifiedBy = 'HARROGATE\Name'
  AND NULLIF(DeletedDate, 0) IS NULL;

Upvotes: 2

Related Questions