Feature request: $COLUMNS()$ macro

The $COLUMNS()$ macro has settings that allow retrieving only key/updatable columns but it lacks their inverts, that is, all columns except key/updatable columns so when you need those you have to get them the hard way.

Also, the text-prefixes and suffixes for the macro variable behave differently depending on the location of the macro. The following snippet constructs a merge statement for the selected table/view:

Code:

$PROMPT(output,Output?,,Kihajtották a mergéket a pályára :),--O O,No Yes)$
$PROMPT(matched,When matched?,,Kihajtották a mergéket a pályára :),Nothing "CU - Conditional update" "UU - Unconditional update" "CD - Conditional delete" "UD - Unconditional delete" "CUUD - Conditional update + unconditional delete" "CDUU - Conditional delete + unconditional update")$
$PROMPT(notmatched,When NOT MATCHED BY SOURCE?,,Kihajtották a mergéket a pályára :),Nothing "CU - Conditional update" "UU - Unconditional update" "CD - Conditional delete" "UD - Unconditional delete" "CUUD - Conditional update + unconditional delete" "CDUU - Conditional delete + unconditional update")$

MERGE INTO $OBJECT(ins_qualname, table, view)$ AS t
USING (
   |
   ) AS s
ON "AND t."$COLUMNS(vertical, keys)$" = s."$COLUMNS(vertical, keys)$
$$
DECLARE
@condition NVARCHAR(MAX)
,@delete NVARCHAR(MAX) = 'DELETE'
,@update NVARCHAR(MAX)
,@matched NVARCHAR(12) = 'WHEN MATCHED'
,@notmatched NVARCHAR(26) = 'WHEN NOT MATCHED BY SOURCE'

SET @condition = ' AND
(
"OR t."$COLUMNS(vertical,updatable)$ != s.$COLUMNS(vertical,updatable)$
) '

SET @delete = ' THEN
DELETE
'

SET @update = ' THEN
UPDATE
SET "t."$COLUMNS(vertical,updatable)$ = s.$COLUMNS(vertical,updatable)$
'

SELECT
CASE '$matched$'
   WHEN 'Nothing'    THEN ''
   WHEN 'CU - Conditional update'    THEN @matched + @condition + @update
   WHEN 'UU - Unconditional update'    THEN @matched + @update
   WHEN 'CD - Conditional delete'    THEN @matched + @condition + @delete
   WHEN 'UD - Unconditional delete'    THEN @matched + @delete
   WHEN 'CUUD - Conditional update + unconditional delete' THEN @matched + @condition + @update + @matched + @delete
   WHEN 'CDUU - Conditional delete + unconditional update' THEN @matched + @condition + @delete + @matched + @update
   ELSE 'geba'
END

SELECT '
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
$COLUMNS(vertical, updatable)$
)
VALUES
(
s.$COLUMNS(vertical)$
)
'
SELECT
CASE '$notmatched$'
   WHEN 'Nothing'    THEN ''
   WHEN 'CU - Conditional update'    THEN @notmatched + @condition + @update
   WHEN 'UU - Unconditional update'    THEN @notmatched + @update
   WHEN 'CD - Conditional delete'    THEN @notmatched + @condition + @delete
   WHEN 'UD - Unconditional delete'    THEN @notmatched + @delete
   WHEN 'CUUD - Conditional update + unconditional delete' THEN @notmatched + @condition + @update + @notmatched + @delete
   WHEN 'CDUU - Conditional delete + unconditional update' THEN @notmatched + @condition + @delete + @notmatched + @update
   ELSE 'geba'
END
$$
$output$UTPUT $$SELECT CHAR(36)$$action, INSERTED.*, DELETED.*
;

Applying it to a test table with a two-column composite primary key and an additional varchar data column, the result is the following SQL code

Code:

MERGE INTO dbo.test AS t
USING (

) AS s
ON t.id_test_field1 = s.id_test_field1
AND t.id_test_field2 = s.id_test_field2
WHEN MATCHED AND
(
OR t.id_test_field1 != s.id_test_field1,
OR t.id_test_field2 != s.id_test_field2,
OR t.test_data != s.test_data
) THEN
UPDATE
SET t.id_test_field1 = s.id_test_field1,
t.id_test_field2 = s.id_test_field2,
t.test_data = s.test_data

WHEN NOT MATCHED BY TARGET THEN
INSERT
(
id_test_field1,
id_test_field2,
test_data
)
VALUES
(
s.id_test_field1,
s.id_test_field2,
s.test_data
)
WHEN NOT MATCHED BY SOURCE THEN
DELETE

OUTPUT $action, INSERTED.*, DELETED.*
;

As you can see, when the macro is placed simply in the snippet then it works more or less as it was wished for. The first one is placed at the 'ON' part of the MERGE statement and uses double quotes to prefix the AND keyword and "t." to the columns with the second one concatenated immediately after the '=', prefixing "s." to the column names. The result has no AND keyword prefixed to the first line found but it's present at the subsequent lines. Besides, while AND is missing, the "t." is not. Though this is a bit strange and does not meet the documentation it is exactly what I needed here. Furthermore, there are no commas at the end of the result lines.

The next (pair of) occurrence (at the SET @condition part), which is nested into an $$...$$ macro, is closer to the documentation, meaning that both the OR keyword and "t." is prefixed to the first item, though the OR keyword is very much unwanted here. In addition, in this case, when the macro is nested and placed at the end of the line there are commas suffixed to end of the result lines and nothing I tried could convince it to omit them.

It would be nice to have greater control over the results, eg. you could set the separator using the text-prefix/suffix, options that prefix/suffix should be attached to first/last item, retrieved column types (keys/not keys, updatables/not updatables, etc.)

I have l; togged an enhancement request for returning non-key columns only.

I think it would be better to handle suffixes and prefixes as options to the $COLUMNS$ and other macros macro not as results of dynamic text parsing that can be affected by injections of previous results and difficult to handle reliably.

I agree. I tried to use another macro as a prefix to this one and the result was... well it made quite a mess. But this macro will have great potential with the proper options implemented.

Maybe the next step in SA evolution could be the ability to create and reuse user defined macros. I know there's $$...$$ but if it could be assigned a macro name and have user defined options as well (requiring some syntax change or whatever) and it could be reused in snippets instead of having to copy-paste existing ones over and over again, it would give the versatility of SA a tremendous boost. A vast improvement, yes, it would be.

That sounds like a great idea. Let me submit an enhancement request for that one.