Options
SQL Format on a merge statement with multiple case statements
dkekish
Posts: 7 Bronze 2
in SQL Prompt
Looking for a way to alter the format style to correctly layout what amounts to multiple case statements in a sql merge statement, for example:
)
)
MERGE targetTable AS t
USING sourceTable AS s
ON (t.keyfield = s.keyfield)
WHEN MATCHED AND t.HashBytes <> s.Hashbytes THEN
UPDATE Set ...... /* the update statement is formatted correctly)
WHEN NOT MATCHED BY TARGET THEN
UPDATE Set ...... /* the update statement is formatted correctly)
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
column1,
column2,
column3)
VALUES
(
CASE
WHEN s.Column1 = '' THEN
NULL
ELSE
s.Column1
END, s.Column2, s.Column3 /* why are these not on separate lines? */)
Tagged:
Best Answer
-
Options
Jon_Kirkwood
Posts: 336 Silver 4
Hi @dkekish
Thank you for reaching out on the Redgate forums regarding your SQL Prompt layout query.
I think it may be worth looking at the INSERT section of the Formatting styles (Formatting styles > Clauses > INSERT
There is a setting 'Place subsequent values on new lines'
Setting this to 'Always' does seem to change that nested behavior in your code snippets.
You can further modify the behavior of each case statement through Formatting styles > Expressions > CASE
This should tackle your two examples with non-case inserts appearing on the same line
and also your example with 3 case checks
Is this the type of behavioral change you are wanting to see with your style?
Jon Kirkwood | Technical Support Engineer | Redgate Software
Answers