Case When Then

torsten.strauss

Hi Redgate Team,

the following code is formatted like
[code]
SELECT
 sya.id
  , sya.name AS alerter_name
  , sya.event_source
  , sya.event_category_id
  , sya.event_id
  , sya.message_id
  , sya.severity AS actual_severity
  , CASE WHEN sya.enabled = 1 THEN 'enabled' ELSE 'disabled' END AS actual_status
  , sya.delay_between_responses AS actual_delay_between_responses
  , sya.include_event_description AS actual_include_event_description
  , sya.database_name AS actual_database_name
  , sya.job_id AS actual_job_id
  , sya.category_id
  , syo.name AS operator_name
  , CASE syn.notification_method WHEN 1 THEN 'E-mail' WHEN 2 THEN 'Pager' WHEN 4
                      THEN 'netsend' WHEN 7
                           THEN 'All' END AS notification_method
FROM
 msdb.dbo.sysalerts sya
LEFT JOIN
 msdb.dbo.sysnotifications syn
ON
 sya.id = syn.alert_id
LEFT JOIN
 msdb.dbo.sysoperators syo
ON
 syn.operator_id = syo.id
WHERE
 sya.name = @alter_name;
[/code]

You can see that the case when then is not formatted well.
I was trying to change the formatting options without any luck.

It should look like  

[code]
  , CASE syn.notification_method
  WHEN 1 THEN 'E-mail'
  WHEN 2 THEN 'Pager'
  WHEN 4 THEN 'netsend'
  WHEN 7 THEN 'All' END AS notification_method
FROM
 msdb.dbo.sysalerts sya
[/code]

Thanks for fixing it!

Torsten 

Tianjiao_Li

Hi @torsten.strauss

It looks like you need to uncheck 'Collapse CASE expressions shorter than characters'. With regarding to make THEN expression on the same line, this is unfortunately not possible at the moment so please cast your vote on this existing post on our user voice forum https://redgate.uservoice.com/forums/94413-sql-prompt/suggestions/36543346-then-expression-on-same-line-as-keyword