CODEWITHSUNDEEP
sql-server
Igor
Igor

Reputation: 6245

Conditional NULL SELECT

What is the proper syntax to conditionally query value with the NULL check?

DECLARE @TargetDialogHandle UNIQUEIDENTIFIER
DECLARE @EventMessage XML
DECLARE @EventMessageTypeName sysname

WAITFOR(
    RECEIVE @TargetDialogHandle = conversation_handle,
    @EventMessage = CONVERT(XML, message_body),
    @EventMessageTypeName = message_type_name FROM dbo.EventNotificationQueue ), TIMEOUT 1000

SELECT 
    @EventMessage.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)') AS TSQLCommand, 
    CASE @EventMessage.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'varchar(128)') 
       WHEN NULL @EventMessage.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(128)') 
       ELSE @EventMessage.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'varchar(128)') 
    END AS TableName

This throws an error:

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '@EventMessage'.

The statement below:

DECLARE @TargetDialogHandle UNIQUEIDENTIFIER
DECLARE @EventMessage XML
DECLARE @EventMessageTypeName sysname

WAITFOR(
    RECEIVE @TargetDialogHandle = conversation_handle,
    @EventMessage = CONVERT(XML, message_body),
    @EventMessageTypeName = message_type_name FROM dbo.EventNotificationQueue ), TIMEOUT 1000

SELECT 
    @EventMessage.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)') AS TSQLCommand, 
    IIF( @EventMessage.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'varchar(128)') IS NULL, @EventMessage.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(128)'), @EventMessage.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'varchar(128)')) AS TableName

returns

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'IS'.

Upvotes: 0

Views: 112

Answers (2)

B.Porter
B.Porter

Reputation: 367

Can't you just use a COALESCE?

DECLARE @TargetDialogHandle UNIQUEIDENTIFIER
DECLARE @EventMessage XML
DECLARE @EventMessageTypeName sysname

WAITFOR(
    RECEIVE @TargetDialogHandle = conversation_handle,
    @EventMessage = CONVERT(XML, message_body),
    @EventMessageTypeName = message_type_name FROM dbo.EventNotificationQueue ), TIMEOUT 1000

SELECT 
    @EventMessage.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)') AS TSQLCommand, 
    COALESCE(@EventMessage.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'varchar(128)'), @EventMessage.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(128)')) AS TableName

Upvotes: 0

marc_s
marc_s

Reputation: 754230

There are two styles for the CASE expression:

CASE (column name or SQL variable)
   WHEN (value 1) 
      THEN (return value 1)
   WHEN (value 2) 
      THEN (return value 2)       
   ELSE (return else value)
END

This checks if the column or variable name after CASE is equal to one of the given values and returns something in each case.

However, if you cannot check for equality - and with NULL, you never can! - you need to use the other style: 

CASE 
   WHEN (expression 1) 
      THEN (return value 1)
   WHEN (expression 2) 
      THEN (return value 2)       
   ELSE (return else value)
END

So here you check formulate expressions (that can use something other than just equality checks - e.g. checks for >=, or with BETWEEN for ranges etc.).

In your case, since you want to check for NULL, you must use approach #2:

SELECT 
    @EventMessage.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)') AS TSQLCommand, 
    CASE 
       -- check if your value IS NULL
       WHEN @EventMessage.value(.....) IS NULL 
          THEN @EventMessage.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(128)') 
       ELSE @EventMessage.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'varchar(128)') 
    END AS TableName

Upvotes: 2

Related Questions