SQL查询排序规则冲突解决：如何在SELECT语句中设置排序规则

Got it, let's sort out this collation conflict error you're hitting. The issue pops up because the data you're comparing in the WHERE clause uses two different collations—SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AI. To fix this, we just need to enforce the same collation (SQL_Latin1_General_CP1_CI_AI) on both sides of the comparison.

Here's the modified SQL code with the collation applied where it matters:

SET @ColumnNames_CSV_Responses_Smoker_Retail = ' ';
SELECT @ColumnNames_CSV_Responses_Smoker_Retail = COALESCE(@ColumnNames_CSV_Responses_Smoker_Retail, '') + CASE 
WHEN QuestionType IN (2, 3, 4) AND EXISTS (SELECT * FROM Optiontable WHERE Option_Code = '1001' AND Optiontable.Question_ID = Question.QuestionID) 
THEN 'dbo.ReplaceBrandIDWithCode(''' + CAST(QuestionID AS varchar) + ''',[' + CAST(QuestionID AS varchar) + ']) AS [' + CAST(QuestionID AS varchar) + '],'
WHEN QuestionType IN (3) 
THEN 'REPLACE(RTRIM(LTRIM([' + CAST(QuestionID AS varchar) + '])),'' '', ''#'') AS [' + CAST(QuestionID AS varchar) + '],'
WHEN QuestionType IN (4) 
THEN 'REPLACE(REPLACE(SUBSTRING(RTRIM(LTRIM([' + CAST(QuestionID AS varchar) + '])),1, LEN(RTRIM(LTRIM([' + CAST(QuestionID AS varchar) + ']))) - 1),''^'', ''#''),'','','''') AS [' + CAST(QuestionID AS varchar) + '],'
ELSE 'REPLACE(RTRIM(LTRIM([' + CAST(QuestionID AS varchar) + '])),'','', '' '') AS [' + CAST(QuestionID AS varchar) + '],'
END 
FROM Question 
WHERE CAST(QuestionID AS varchar) COLLATE SQL_Latin1_General_CP1_CI_AI 
IN (SELECT COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AI FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table_Name) 
ORDER BY QuestionSeqNumber;

What changed? #

I added COLLATE SQL_Latin1_General_CP1_CI_AI to both:

• The CAST(QuestionID AS varchar) value from the Question table
• The COLUMN_NAME retrieved from INFORMATION_SCHEMA.COLUMNS

By aligning the collation on both sides of the IN comparison, we eliminate the mismatch that was causing the error. This ensures SQL Server can properly compare the two sets of values without collation conflicts.

内容的提问来源于stack exchange，提问作者manoj soma