如何让MSSQL查询自动检测数据库排序规则适配执行？

Absolutely, your goal to build a single query that adapts to the collation of the PracticeEvolve_c1 database is totally achievable! Let's fix up your syntax issues and implement this properly.

First, let's spot the problems in your sample code:

• The DATABASEPROPERTYEX function only needs the database name (not PracticeEvolve_c1.dbo) as its first parameter.
• You don't need to prefix the collation value with COLLATE when comparing the result of DATABASEPROPERTYEX.
• Your CASE expression structure has misplaced AS FirstName/AS LastName clauses — the alias belongs at the end of the entire CASE expression, not inside each branch.

Correct Implementation #

Here's a cleaned-up, working version of your query logic:

DECLARE @DbCollation NVARCHAR(128) = DATABASEPROPERTYEX('PracticeEvolve_c1', 'Collation');

SELECT
    FirstName = CASE
        WHEN @DbCollation = 'SQL_Latin1_General_CP1_CI_AS' THEN
            CASE 
                WHEN [dd_entity_d2].[type] COLLATE SQL_Latin1_General_CP1_CI_AS = 'Individual' 
                THEN etClient.FirstName COLLATE SQL_Latin1_General_CP1_CI_AS 
                ELSE [dd_entity_d4].[firstname] COLLATE SQL_Latin1_General_CP1_CI_AS 
            END
        ELSE
            CASE 
                WHEN [dd_entity_d2].[type] = 'Individual' 
                THEN etClient.FirstName 
                ELSE [dd_entity_d4].[firstname] 
            END
    END,
    LastName = CASE
        WHEN @DbCollation = 'SQL_Latin1_General_CP1_CI_AS' THEN
            CASE 
                WHEN [dd_entity_d2].[type] COLLATE SQL_Latin1_General_CP1_CI_AS = 'Individual' 
                THEN etClient.LastName COLLATE SQL_Latin1_General_CP1_CI_AS 
                ELSE [dd_entity_d4].[lastname] COLLATE SQL_Latin1_General_CP1_CI_AS 
            END
        ELSE
            CASE 
                WHEN [dd_entity_d2].[type] = 'Individual' 
                THEN etClient.LastName 
                ELSE [dd_entity_d4].[lastname] 
            END
    END
-- Add your FROM/JOIN/WHERE clauses here

Key Notes: #

• Store Collation in a Variable: By fetching the database collation once into @DbCollation, we avoid redundant calls to DATABASEPROPERTYEX and make the query cleaner.
• Proper Collation Comparison: The result of DATABASEPROPERTYEX('PracticeEvolve_c1', 'Collation') is a string like SQL_Latin1_General_CP1_CI_AS, so we compare it directly without the COLLATE keyword.
• Alias Placement: The AS FirstName (or using the FirstName = ... syntax) is applied to the entire CASE expression, not each inner branch — this fixes the syntax error in your original sample.
• Targeted Collation Application: Only when the database uses SQL_Latin1_General_CP1_CI_AS do we explicitly apply that collation to the fields and string literal to avoid mismatch errors. For other collations, we rely on the default collation of the fields.

Alternative Simplification #

If the only collation you need to handle is SQL_Latin1_General_CP1_CI_AS, you could even simplify the inner CASE logic to reuse the collation dynamically, though readability might suffer a bit:

DECLARE @TargetCollation NVARCHAR(128) = 'SQL_Latin1_General_CP1_CI_AS';
DECLARE @UseCollation BIT = CASE WHEN DATABASEPROPERTYEX('PracticeEvolve_c1', 'Collation') = @TargetCollation THEN 1 ELSE 0 END;

SELECT
    FirstName = CASE
        WHEN @UseCollation = 1 THEN
            CASE 
                WHEN [dd_entity_d2].[type] COLLATE @TargetCollation = 'Individual' COLLATE @TargetCollation
                THEN etClient.FirstName COLLATE @TargetCollation
                ELSE [dd_entity_d4].[firstname] COLLATE @TargetCollation
            END
        ELSE
            CASE 
                WHEN [dd_entity_d2].[type] = 'Individual' 
                THEN etClient.FirstName 
                ELSE [dd_entity_d4].[firstname] 
            END
    END,
    LastName = CASE
        WHEN @UseCollation = 1 THEN
            CASE 
                WHEN [dd_entity_d2].[type] COLLATE @TargetCollation = 'Individual' COLLATE @TargetCollation
                THEN etClient.LastName COLLATE @TargetCollation
                ELSE [dd_entity_d4].[lastname] COLLATE @TargetCollation
            END
        ELSE
            CASE 
                WHEN [dd_entity_d2].[type] = 'Individual' 
                THEN etClient.LastName 
                ELSE [dd_entity_d4].[lastname] 
            END
    END
-- Add your FROM/JOIN/WHERE clauses here

This way, if you ever need to adjust the target collation, you only have to change the @TargetCollation variable once.

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