where to add a COLLATION in an SPROC
- by Daria
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
