title: SQL Server Collation problem (Msg 468, Level 16, State 9) in IF statement
body: <p>My problem is occurring with a "simple-as-it-gets" IF statement, making the suggested fixes to many similar questions (e.g. <a href="https://stackoverflow.com/questions/27144684/cannot-resolve-the-collation-conflict-in-my-query">Cannot resolve the collation conflict in my query</a>) seemingly useless.</p>
  
  <p>The error message is :</p>
  
  <blockquote>
    <p>Msg 468, Level 16, State 9, Procedure #XYZ, Line 11<br>
    Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.</p>
  </blockquote>
  
  <p>It's known that the server collation is set to <code>SQL_Latin1_General_CP1_CI_AS</code>. </p>
  
  <p>This query demonstrates the problem :</p>
  
  <pre><code>-- 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
  </code></pre>
  
  <p>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 <a href="https://www.mssqltips.com/sqlservertip/4395/understanding-the-collate-databasedefault-clause-in-sql-server/" rel="nofollow noreferrer">https://www.mssqltips.com/sqlservertip/4395/understanding-the-collate-databasedefault-clause-in-sql-server/</a> and <a href="https://stackoverflow.com/questions/12028033/cannot-resolve-the-collation-conflict-between-temp-table-and-sys-objects">Cannot resolve the collation conflict between temp table and sys.objects</a>).</p>
  
  <p>Closely related links:</p>
  
  <p>Documentation of the COLLATE clause: <a href="https://docs.microsoft.com/en-us/sql/t-sql/statements/collations?view=sql-server-2017" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/sql/t-sql/statements/collations?view=sql-server-2017</a></p>
  
  <p>A solution that I probably cannot use:  <a href="https://www.mssqltips.com/sqlservertip/2901/how-to-change-server-level-collation-for-a-sql-server-instance/" rel="nofollow noreferrer">https://www.mssqltips.com/sqlservertip/2901/how-to-change-server-level-collation-for-a-sql-server-instance/</a></p>