带有可以是 varchar 或 uniqueidentifier 的标量变量的 SQL Server 查询

一尘不染

带有可以是 varchar 或 uniqueidentifier 的标量变量的 SQL Server 查询

sql

下面的查询有一个标量变量@LocationID，它可以是 avarchar或 a ，uniqueidentifier具体取决于标量变量的值@LimitTo。

此查询在添加行之前有效

OR ((@LimitTo = 'Drawing') AND (ba.DrawingGuid = ...

DrawingGuid是一个Guid而不是像其他 OR 语句那样的文本。

这表明在运行查询之前分析了 SQL 并选择了单个转换方法，并且看到有两种可能性，当我使用@LocationIDas a时会引发以下错误Varchar（如果@LocationIDis a则可以正常工作uniqueidentifier）

从字符串转换为唯一标识符时转换失败

不过，我不确定这个理论是否正确。有没有办法让@LocationID变量成为这个查询的 varchar 或 uniqueidentifier？

这是查询：

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'; --'2FB87868-D5D7-4A84-916F-F1DEE871A085'; 'North'

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 (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 (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 (ba.DrawingGuid = CONVERT(uniqueidentifier, @LocationID))))
       AND ba.Deleted = 0) AS AllowanceQuantity
FROM 
    Assembly asm
WHERE 
    asm.Deleted = 0
ORDER BY 
    asm.AssemblyCode, asm.AssemblyRestorationDesc, 
    asm.AssemblyUnit, asm.AssemblyGuid

阅读 108

2022-07-21

共1个答案

一尘不染

您正在尝试将不同的列与进行比较@locationId，而要比较的列取决于的值@limitTo。其他列（如AllowanceWorkZone）可能是 all varchar，但在@limitTo = 'Drawing'您想要@LocationId与DrawingGuid列进行比较的情况下，它是 a uniqueidentifier。

不，你不能这样做。

只需声明第二个变量a，uniqueidentifier并在与列的比较中使用它DrawingGuid…

declare @LocationVarchar varchar(36) = 'North';
declare @LocationGuid uniqueidentifier = try_cast(@LocationVarchar as uniqueidentifier);
declare @LimitTo varchar(30) = 'foo';

select ...
from   ...
where  (@LimitTo = 'foo' and MyVarcharColumn = @locationVarchar)
       or (@LimitTo = 'bar' and MyGuidColumn = @locationGuid);

在上面的代码中，如果传入的过滤器值不是一个有效的uniqueidentifier，那么@locationGuid就会是null，但没关系，因为MyVarcharColumn反对的比较@LocationVarchar是相关的谓词。

当然，您也可以try_cast直接在谓词中使用，但我将其分离到一个单独的变量中只是为了更清楚地说明发生了什么。

编辑：如果没有第二个变量，它将是......

where (@limitTo = 'foo' and MyVarcharColumn = @locationVarchar)
      or (@limitTo = 'bar' and MyGuidColumn = try_cast(@locationVarchar as uniqueidentifier);

2022-07-21