SQL Server query with a scalar variable that can be a varchar or uniqueidentifier-CodePudding

The query below has a scalar variable @LocationID that can either be a varchar or a uniqueidentifier depending on the value of the scalar variable @LimitTo.

This query worked before the addition of the addition of the lines

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

DrawingGuid is a Guid and not text like the other OR statements.

This suggests that the SQL is analyzed and selects a single conversion method ahead of running the query, and seeing that there are two possibilities, throws the following error when I use @LocationID as a Varchar (it works fine if @LocationID is a uniqueidentifier)

Conversion failed when converting from a character string to uniqueidentifier

Though, I'm not sure if this theory is correct. Is there a way to have the @LocationID variable either be a varchar or uniqueidentifier for this query?

Here's the query:

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

CodePudding user response：

I think I understand what you are trying to do.

You are trying to compare different columns to the @locationId, and the column you want to compare depends on the value of @limitTo. The other columns (like AllowanceWorkZone) are presumably all varchar, but in cases where @limitTo = 'Drawing' you instead want to compare @LocationId with the DrawingGuid column, which is a uniqueidentifier.

No, you can't do this.

Just declare a second variable which is a uniqueidentifier, and use that in the comparison against the DrawingGuid column...


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);

In the above code, if the incoming filter value is not a valid uniqueidentifier, then @locationGuid will be null, but it won't matter, because the comparison of MyVarcharColumn against @LocationVarchar is the relevant predicate.

You could of course also use try_cast directly in the predicate, but I separated it out to a separate variable just to make it clearer what was going on.

Edit: Without the second variable, it would be...

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