Cannot Resolve Collation Conflict for Column n in SELECT Statement

When creating a stored procedure and it contains a SELECT statement, you may get the following error message even if the collation of your database seems to be correct.

Cannot resolve collation conflict for column n in SELECT statement.

where n is the column number.

This can be easily fixed by appending the following portion of SQL to the column n in your SELECT statement:

COLLATE <your database collation name>

For example, COLLATE SQL_Latin1_General_CP1_CI_AS.