SQL Server查询varchar转uniqueidentifier失败兼容方案

该报错由SQL Server两个固有运行机制共同导致：

1. 执行计划生成阶段无分支短路预判：SQL Server不会因谓词中存在@LimitTo = 'Drawing'的前置判断，就跳过其他OR分支的类型校验，所有出现在谓词中的比较表达式都会被纳入执行计划的类型推导逻辑，不存在“运行时走到对应分支才校验类型”的逻辑。
2. 数据类型优先级规则：SQL Server中uniqueidentifier类型的优先级高于varchar类型，当比较运算符两侧数据类型不一致时，引擎会自动将低优先级类型的值转换为高优先级类型后再做匹配。

查询中三个逻辑点共同触发了报错：

• Marker、BaselineMarker子查询中，m.DrawingGuid/bm.DrawingGuid为uniqueidentifier类型，与varchar类型的@LocationID直接比较时，SQL Server会默认尝试将@LocationID转换为uniqueidentifier类型
• BaselineAllowance子查询中显式编写了CONVERT(uniqueidentifier, @LocationID)逻辑，该转换为强制生效逻辑，与@LimitTo的取值完全无关
• 当@LocationID传入'North'这类不符合GUID格式的字符串时，转换逻辑直接抛出错误，哪怕当前@LimitTo取值不是'Drawing'，根本不会触发Drawing相关的匹配分支，也会中断查询执行，报错信息如下：

从字符串转换为 uniqueidentifier 时转换失败。

可根据业务场景选择以下任意一种方案，优先推荐改动成本最低的方案一：

方案一：统一将uniqueidentifier字段转为字符串类型比较 #

将所有与@LocationID做等值匹配的GUID字段，显式转换为长度匹配的varchar类型，让比较两侧均为字符串类型，彻底绕开类型优先级触发的隐式转换。
修正后的完整查询代码如下：

DECLARE @Contract VARCHAR(60);
SET @Contract = 'F8C018CA-A00C-4BB1-B920-D460786F6820';

DECLARE @LimitTo VARCHAR(30);
SET @LimitTo = 'WorkZone';-- 可选值: 'Drawing', 'WorkZone'

DECLARE @LocationID VARCHAR(60);
SET @LocationID = 'North'; -- 可传入普通区域名或合法GUID格式字符串

SELECT DISTINCT      
    asm.AssemblyCode,
    asm.AssemblyRestorationDesc,
    asm.AssemblyUnit,
    asm.AssemblyGuid,
    (SELECT SUM(m.MarkerQuantity) 
     FROM Marker m 
     WHERE m.AssemblyGuid = asm.AssemblyGuid
       AND m.MarkerExcludeFromScope = 'False'
       AND m.ContractGuid = @Contract
       AND (((@LimitTo = 'WorkZone') AND (m.MarkerWorkZone = @LocationID))
            OR ((@LimitTo = 'WorkRegion') AND (m.MarkerWorkRegion = @LocationID))
            OR ((@LimitTo = 'Drawing') AND (CONVERT(VARCHAR(36), m.DrawingGuid) = @LocationID)))
       AND m.Deleted = 0) AS Quantity,
    (SELECT SUM(bm.MarkerQuantity) 
     FROM BaselineMarker bm  
     WHERE bm.AssemblyCode = asm.AssemblyCode
       AND bm.MarkerExcludeFromScope = 'False'
       AND (((@LimitTo = 'WorkZone') AND (bm.MarkerWorkZone = @LocationID))
            OR ((@LimitTo = 'WorkRegion') AND (bm.MarkerWorkRegion = @LocationID))
            OR ((@LimitTo = 'Drawing') AND (CONVERT(VARCHAR(36), bm.DrawingGuid) =  @LocationID)))
       AND bm.Deleted = 0) AS BaselineQuantity,
    (SELECT SUM(ba.AllowanceQuantity) 
     FROM BaselineAllowance ba
     WHERE ba.AssemblyCode = asm.AssemblyCode
       AND (((@LimitTo = 'WorkZone') AND (ba.AllowanceWorkZone = @LocationID))
            OR ((@LimitTo = 'WorkRegion') AND (ba.AllowanceWorkRegion = @LocationID))
            OR ((@LimitTo = 'Drawing') AND (CONVERT(VARCHAR(36), ba.DrawingGuid) = @LocationID)))
       AND ba.Deleted = 0) AS AllowanceQuantity
FROM 
    Assembly asm
WHERE 
    asm.Deleted = 0
ORDER BY 
    asm.AssemblyCode, asm.AssemblyRestorationDesc, 
    asm.AssemblyUnit, asm.AssemblyGuid

注意：GUID的标准字符串格式长度固定为36位，将GUID字段转为varchar后做等值匹配不会出现匹配错误，可同时兼容普通区域名字符串、合法GUID字符串两类入参。

方案二：使用动态SQL拼接匹配逻辑（性能最优） #

根据@LimitTo的入参，动态拼接仅包含对应匹配分支的SQL语句，让最终执行的查询中不存在跨类型比较逻辑，同时可以利用对应匹配字段上的索引，查询性能更优，适合数据量较大的业务场景。
核心实现逻辑示例：

DECLARE @sql NVARCHAR(MAX);
-- 按@LimitTo取值拼接对应过滤条件，完全移除不相关的分支逻辑
DECLARE @filterClause NVARCHAR(200) = 
CASE @LimitTo
    WHEN 'WorkZone' THEN N'm.MarkerWorkZone = @LocationID'
    WHEN 'WorkRegion' THEN N'm.MarkerWorkRegion = @LocationID'
    WHEN 'Drawing' THEN N'm.DrawingGuid = CONVERT(uniqueidentifier, @LocationID)'
END;

SET @sql = N'
SELECT DISTINCT      
    asm.AssemblyCode,
    asm.AssemblyRestorationDesc,
    asm.AssemblyUnit,
    asm.AssemblyGuid,
    (SELECT SUM(m.MarkerQuantity) 
     FROM Marker m 
     WHERE m.AssemblyGuid = asm.AssemblyGuid
       AND m.MarkerExcludeFromScope = ''False''
       AND m.ContractGuid = @Contract
       AND ' + @filterClause + N'
       AND m.Deleted = 0) AS Quantity
    -- BaselineMarker、BaselineAllowance子查询使用相同逻辑拼接对应过滤条件即可
FROM Assembly asm
WHERE asm.Deleted = 0
ORDER BY asm.AssemblyCode, asm.AssemblyRestorationDesc, asm.AssemblyUnit, asm.AssemblyGuid
'
-- 通过参数化执行动态SQL，避免SQL注入风险
EXEC sp_executesql @sql, N'@Contract VARCHAR(60), @LocationID VARCHAR(60)', @Contract, @LocationID

内容的提问来源于stack exchange，提问作者E. A. Bagby