where to add a COLLATION in an SPROC

Question

i've got a collation error happening in a stored procedure in SQL Server.

Cannot resolve the collation conflict between "Latin1_General_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

The database's collation is Latin1_General_CS_AS

The error happens on the INSERT INTO line. Where should i add a COLLATE statement?

CREATE TABLE #TempList
(
    TNR varchar(10)
)

DECLARE @TNR varchar(10), @Pos int

SET @subjectList = LTRIM(RTRIM(@subjectList))+ ','
SET @Pos = CHARINDEX(',', @subjectList, 1)

IF REPLACE(@subjectList, ',', '') <> ''
BEGIN
    WHILE @Pos > 0
    BEGIN
        SET @TNR = LTRIM(RTRIM(LEFT(@subjectList, @Pos - 1)))
        IF @TNR <> ''
        BEGIN
            INSERT INTO #TempList (TNR) VALUES (CAST(@TNR AS varchar(10))) --this is where it errors
        END
        SET @subjectList = RIGHT(@subjectList, LEN(@subjectList) - @Pos)
        SET @Pos = CHARINDEX(',', @subjectList, 1)

    END
END 

Answer 1

temp tables use the tempdb server collation which is the server collation.

By using COLLATE Database_Default you change it to use the "host" database collation and make the collation coercion independent of the actual DB collation

CREATE TABLE #TempList
(
    TNR varchar(10) COLLATE Database_Default
)
...

Not for @TNR (even though a string data type) because this obviously has the same collation as the DB (not server) hence the error...