Collation conflict when creating stored procedure

I'm trying to create a stored procedure but it gives me the error message

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN2" in the EXCEPT operation.

The thing is that both the database and server collation is SQL_Latin1_General_CP1_CI_AS and I have no clue where the Latin1_General_BIN2 collation comes from.

create procedure [ETL_1.4.0].update_valve_event_type
(
    @data nvarchar(max)
)
as
    declare @mismatch_table table(id int, [name] varchar(50))

    if isjson(@data) = 0
    begin
        ;throw 50000,'Input argument @data is invalid JSON.', 1
    end

    insert @mismatch_table (id, [name])
    select * from
    (
        select
            value as id,
            [key] as [name]
        from openjson(@data)
        except
        select
            id,
            [name]
        from enum.valve_event
    )data

    --clear mismatches that are deemed ok, e.g. spelling corrections
    delete from @mismatch_table
    where id = 10 and [name] = 'FEEDBACK_FROM_USER'

    if exists(select * from @mismatch_table)
    begin
        ;throw 50000,'Terminal version of enum for valve events does not match the EDW version', 1
    end
go

So can someone explain what I'm missing?

If it matters, this database (and server for that matter) is not live yet so technically I have the ability to change collations wherever I want.

It is likely the output from the OPENJSON function, especially since that is part of an EXCEPT operation. All you need to do is force a collation in that query via the COLLATE option/keyword. For example:
...
[key] COLLATE SQL_Latin1_General_CP1_CI_AS as [name]
from openjson(@data)
...


As for which collation to force, pick one that supports how you want the operation to behave (in simple terms: should 'A' = 'a' or 'A' <> 'a'). The two most likely options are the two collations already being used: Latin1_General_BIN2, and (in this particular case) SQL_Latin1_General_CP1_CI_AS. (And yes, if you choose Latin1_General_BIN2 which is already the collation of the key column, you still need to specify the COLLATE option, as shown above, in order to avoid the error.)

The documentation for OPENJSON() even loosely states that the output uses a BIN2 collation (though it doesn't specify the exact collation, and I'm not going to quote it as the description isn't exactly correct).

The best option is to just ask SQL Server. We can verify the collation of the output columns by storing them in a table that is dynamically created by SQL Server (via SELECT INTO ...) and then checking the properties of the columns in the newly created table:
SELECT *
INTO #CheckCollation
FROM OPENJSON(N'{}'));

SELECT [name], [system_type_id], [collation_name], [is_nullable]
FROM tempdb.sys.columns col
WHERE col.[object_id] = OBJECT_ID(N'tempdb..#CheckCollation');

-- [key] column has collation of Latin1_General_BIN2
-- [value] column has collation of passed-in JSON value:
-- if passing in column, then collation of column, OR
-- if passing in variable or literal, then default collation of database


Alternatively, @Charlieface (in a comment on this answer) was kind enough to remind us that there is an easier way to get this info by using the cool built-in dynamic management function, sys.dm_exec_describe_first_result_set, and provided a working example via db<>fiddle. I updated that example to include the stored procedure version of that function, sys.sp_describe_first_result_set, as well as the example code shown above.

StackExchange Database Administrators Q#319523, answer score: 6