SQL Server xQuery return NULL instead of empty

Question

So in this example I'm trying to return a NULL instead of an empty nvarchar for element2 and element3. I can't seem to find an answer to this anywhere, or if it's even possible. I know I can check .exists() with a nullif of Case/When, but I'd rather not make that check for the sake of performance.

 WqlEventQuery query = new WqlEventQuery();
        query.EventClassName = "__InstanceCreationEvent";
        query.Condition = "TargetInstance ISA 'Win32_NTLogEvent'";
        query.GroupWithinInterval = new TimeSpan(0, 0, 10);
        System.Collections.Specialized.StringCollection collection =
            new System.Collections.Specialized.StringCollection();
        collection.Add("TargetInstance.SourceName");
        query.GroupByPropertyList = collection;
        query.HavingCondition = "NumberOfEvents > 25";

Not sure where SQL Server fits into this, as the code is WMI-related — MartW, Dec 08 '09 at 17:24

score 5 · Accepted Answer · answered Dec 08 '09 at 17:09

5

I'm not sure what language you are writing in from your question, but SQL Server has a NullIf(val, CheckValue) function. If you can include that in the sql you pass to the server that should do the trick:

   Select NullIf(element2, '')

will return a null if element2 is equal to an empty string ('')

answered Dec 08 '09 at 17:09

Charles Bretana

143,358
22
150
216

I've not heard of NullIf, but it seems like isNull would have the same functionality. – Sonny Boy Dec 08 '09 at 17:18
This works great, because if it doesn't match, it returns the value of the xquery instead of null. Excellent, I ruled out nullif too soon. – SamuelWarren Dec 08 '09 at 17:25
3

@Sonny Boy, IsNull(a, b) returns b if a is null, NullIf(a, b) returns Null if a = b – Charles Bretana Dec 08 '09 at 18:18

SQL Server xQuery return NULL instead of empty

1 Answers1

Linked

Related