Setting Expression Collation
Parameters for stored procedures or functions, alias data types, and variables are assigned the default collation of the database. To change the collation of an alias type, you must drop the alias and re-create it.
You can override the default collation of a literal string by using the COLLATE clause. If you do not specify a collation, the literal is assigned the database default collation. You can use DATABASEPROPERTYEX to find the current collation of the database.