How do I add a guid to the where condition?

Question

How do I add a guid to the where condition, I have tried the following two methods but cannot find the correct data

DECLARE @myid uniqueidentifier ;  
SET @myid = '0000397b-c67f-4a3d-850f-865641bd2cd9';  
SELECT TOP 10 *
FROM OPENROWSET( 
    BULK 'adl://bingads-algo-prod-networkprotection-c08.azuredatalakestore.net/shares/adPlatform.AudienceIntelligence.Scoring.Prod/PublicShare/MDSDB/Backup/Collection/P0/dbo.ReachEstimation/1.0/2021/08/dbo.ReachEstimation_2021_08_15.ss', 
    FORMAT = 'SStream',PARSER_VERSION = '2.0') 
    WITH(
        DefinitionId UNIQUEIDENTIFIER,
        EstimationResultXML VARCHAR(5000)
    )
    as rows 
    WHERE 
    DefinitionId = @myid



SELECT TOP 10 *
FROM OPENROWSET( 
    BULK 'adl://bingads-algo-prod-networkprotection-c08.azuredatalakestore.net/shares/adPlatform.AudienceIntelligence.Scoring.Prod/PublicShare/MDSDB/Backup/Collection/P0/dbo.ReachEstimation/1.0/2021/08/dbo.ReachEstimation_2021_08_15.ss', 
    FORMAT = 'SStream',PARSER_VERSION = '2.0') 
    WITH(
        DefinitionId UNIQUEIDENTIFIER,
        EstimationResultXML VARCHAR(5000)
    )
    as rows 
    WHERE 
    CAST(DefinitionId AS UNIQUEIDENTIFIER) = CAST('00000dc2-2cd3-4b38-9b4a-3bf3360588a0' AS UNIQUEIDENTIFIER)

Answer 1

@PRADEEPCHEEKATLA-MSFT
I finally used the lower function to achieve the comparison of guid

like this :

   LOWER(CAST(guid AS NAVRCHAR(100))) = LOWER(N'guidvalue')  

Answer 2

Hello @Andrew Huang (Beyondsoft Corporation) ,

Thanks for the question and using MS Q&A platform.

The uniqueidentifier type is considered a character type for the purposes of conversion from a character expression, and therefore is subject to the truncation rules for converting to a character type. That is, when character expressions are converted to a character data type of a different size, values that are too long for the new data type are truncated.

For more details, refer to uniqueidentifier and also you may checkout the SO thread addressing similar issue.

Hope this helps. Do let us know if you any further queries.

---------------------------------------------------------------------------

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.