Error while using the generate_surrogate_key macro with one field. The concat function requires 2 to 254 arguments

[derik-roby]

Current Behavior

We need to pass minimum 2 fields while calling the dbt_utils.generate_surrogate_key macro from the model.

with listing_hosts as (
    select
        xxxxxxx
    from
       xxxx
),

dim_host as (
    select
        yyyyyy
    from
       yyyy
)
select
    {{ dbt_utils.generate_surrogate_key([
        **'host_id','host_id'**
    ]) }} as host_sid,
    {{ cols_host }}
from dim_host

Expected behavior

Generate surrogate key even if I pass only one field as input to the macro.

with listing_hosts as (
    select
        xxxxxxx
    from
       xxxx
),

dim_host as (
    select
        yyyyyy
    from
       yyyy
)
select
    {{ dbt_utils.generate_surrogate_key([
        **'host_id'**
    ]) }} as host_sid,
    {{ cols_host }}
from dim_host

Error message while passing only one field:

14:52:03  Unhandled error while executing target\run\xxxx\xx\xxxx\xxx\xxxxx.sql
('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near the keyword 'from'. (156) (SQLMoreResults); [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near the keyword 'order'. (156); [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The concat function requires 2 to 254 arguments. (189)")

[lucaslortiz]

The issue is that the default macro creates a concat statement, even if it has only one column. As a quick fix, you can create a macro to generate the surrogate key for SQL server, and add an if statement before using the concat.

{%- macro sqlserver__generate_surrogate_key(field_list) -%}

{%- if var('surrogate_key_treat_nulls_as_empty_strings', False) -%}
    {%- set default_null_value = "" -%}
{%- else -%}
    {%- set default_null_value = '_dbt_utils_surrogate_key_null_' -%}
{%- endif -%}

{%- set fields = [] -%}

{%- for field in field_list -%}

    {%- do fields.append(
        "coalesce(cast(" ~ field ~ " as " ~ dbt.type_string() ~ "), '" ~ default_null_value  ~"')"
    ) -%}

    {%- if not loop.last %}
        {%- do fields.append("'-'") -%}
    {%- endif -%}

{%- endfor -%}

{%- if fields|length > 1 %}
    {{ dbt.hash(dbt.concat(fields)) }}
{%- else -%}
    {{ dbt.hash(fields[0]) }}
{%- endif -%}

{%- endmacro -%}

[dataders]

@lucaslortiz do you mind opening a PR that modifies this repo's sqlserver__generate_surrogate_key?

[sdebruyn]

fixed