SQL Server Collation problem (Msg 468, Level 16, State 9) in IF statement

Question

My problem is occurring with a "simple-as-it-gets" IF statement, making the suggested fixes to many similar questions (e.g. Cannot resolve the collation conflict in my query) seemingly useless.

The error message is :

Msg 468, Level 16, State 9, Procedure #XYZ, Line 11
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

It's known that the server collation is set to SQL_Latin1_General_CP1_CI_AS.

This query demonstrates the problem :

-- this procedure (which gets put into tempdb) is called WITHOUT specifying @Choice  
CREATE PROCEDURE #XYZ 
(
    -- all other parameters removed (none of them have default values)
    @Choice AS NVARCHAR(1) = 'Y' 
)
AS  
BEGIN
    IF (@choice = 'Y')  -- error raised here 
    BEGIN
        DECLARE @NULL_STATEMENT AS int -- only here because there's no "do nothing" statement
    END 
    RETURN
END

How can I fix this, given that altering the default collation of the server (and/or all of the tables) is NOT going to happen AND it is impractical to insert "COLLATE DATABASE_DEFAULT" in all queries, tables, etc. (for this solution see https://www.mssqltips.com/sqlservertip/4395/understanding-the-collate-databasedefault-clause-in-sql-server/ and Cannot resolve the collation conflict between temp table and sys.objects).

Closely related links:

Documentation of the COLLATE clause: https://learn.microsoft.com/en-us/sql/t-sql/statements/collations?view=sql-server-2017

A solution that I probably cannot use: https://www.mssqltips.com/sqlservertip/2901/how-to-change-server-level-collation-for-a-sql-server-instance/

Answer 1

(From the currently accepted answer):

The fix was to change the default parameter from ASCII (varchar) to nvarchar (UTF-8) form

1. No, that did not fix it. That particular change had no effect since the value was converted to NVARCHAR when the T-SQL was parsed (due to the parameter/variable being NVARCHAR) and this error happens at compile time.
2. This issue has absolutely nothing to do with NVARCHAR, UTF-8, or even parameter default values.
3. UTF-8 is not being used here. SQL Server is only seeing UTF-16 as that is what Unicode strings are transferred in by the driver (i.e. the TDS / tabular data stream). And even when using a UTF-8 collation (new in SQL Server 2019), that encoding is only used with VARCHAR types as NVARCHAR is only ever UTF-16 (Little Endian).

The issue you ran into is one of several "odd" behaviors found in temporary stored procedures (both local and global). For temporary stored procs, parameters and variables will always have a collation matching the tempdb collation, while string literals will use the collation of the database where the CREATE PROCEDURE statement was executed. These two collations do not change (for the main T-SQL context of the module) even if you use another database that has a different default collation other than [tempdb] and the database where the temporary proc was created (though dynamic SQL executed in a temporary stored procedure will use the current DB's collation! Fun, eh?).

Thus, as Martin Smith said in a comment on the question, you must have changed the "current" / "active" database when executing the CREATE PROCEDURE statement after prefixing the parameter value with N.

The following example is a simplified version of the code shown in the question, but clearly shows that prefixing the string literals with N does not prevent the error.

Execute the following T-SQL in a database that has a different collation than [tempdb]:

-- The two returned collations need to be different, else no error with CREATE PROC:
SELECT DATABASEPROPERTYEX(N'tempdb', 'collation') AS [tempdb collation],
       DATABASEPROPERTYEX(DB_NAME(), 'collation') AS [current DB collation];


SET NOEXEC ON;

GO
CREATE PROCEDURE #XYZ
(
  @Choice NVARCHAR(5) = N'Y'
)
AS  
BEGIN
  IF (@Choice = N'Y') PRINT 'yup';
END;
GO

SET NOEXEC OFF;

/*
Msg 468, Level 16, State 9, Procedure #XYZ, Line XXXXX [Batch Start Line YYYYY]
   Cannot resolve the collation conflict between "{current_DB_collation}" and
   "{tempdb_collation}" in the equal to operation.
*/

(From the currently accepted answer):

The weird thing is that after running that version I was able to remove the leading N and the query worked without problems.

Correct. That's due to the N prefix not actually having anything to do with the error or fixing it. You were simply in a DB that had a collation of SQL_Latin1_General_CP1_CI_AS which matched the [tempdb] collation.

---

_{I'm working on a blog post detailing several odd behaviors with temporary stored procedures, including this collation stuff. If / when I ever finish it, I will try to remember to update this answer with a link to it.}

Answer 2

I've summarized the answer provided by @Sean Lange (a variation of @Lamak's comment) as it was was deleted before I could accept it.

The problem (for details see comments on original question) was that I hit a gotcha! while moving the working code to a new server. The fix was to change the default parameter from ASCII (varchar) to nvarchar (UTF-8) form :

CREATE PROCEDURE #XYZ 
(
    -- all other parameters removed (none of them have default values)
    @Choice AS NVARCHAR(1) = N'Y'  -- Note the leading N
)
AS 
....

The wierd thing is that after running that version I was able to remove the leading N and the query worked without problems.