Collation conflict ERROR ,Set collation for stored procedure as database default

Question

How can I set for COLLATION for stored procedure as Database default rather than individual column of tables in that procedure .

Cannot resolve the collation conflict between “SQL_Latin1_General_Pref_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation

I am getting collation conflict error as database server collation is different from database collation. The procedure is huge and I don't know on which table column conflict is occuring.

Is it even possible to assign collation for stored procedure such that all columns in that procedure have same collation?

Answer 1

Firstly, collation is about how the textual data is stored within a table and is not at a stored procedure level.

Collation issues occur when textual data is compared from sources where the text collatation is not stored the same. (I am not talking about the data type of text, just string data)

Check the TSQL where clauses or table joins, this is the most common places where text compares occur. You can collate to the database default by using the collate function next to the compare area. For example

SELECT
c.CustomerID
FROM
dbo.Customer c
INNER JOIN dbo.CustomerLog cl on c.CustomerName = cl.LogEntry collate     
database_default

Depending on your circumstance this might not be possible but I always follows these rules.
- Choose the correct server default collation at install so the tempdb is created under the collation, when you create temp tables, these are created in the tempdb
- when creating tables do not specifiy the collation unless you have specific cause to