SQL – uniqueidentifier in where clause

June 29, 2023

I am using ssms – 18
SQL Version – Microsoft SQL Azure RTM 12.0.200.8

I have 3500 unique identifiers to compare or to keep in where clause.

select * from tableName
where columnName in
(
'6B29FC40-CA47-1067-B31D-00DD010662DA',
'7C59FC31-SA87-1667-B31D-00DD010772DA'
.
.
.
)

columnName is unique identifier here.

Like this I have 3500 unique identifiers to compare in where clause.

I am getting error –

Conversion failed when converting from a character to uniqueidentifier

when I use convert(nvarchar(36,columnName)) in query – Query keeps running for 5 minutes but dont give any result

>Solution :

Please try the following solution.

Overall, casting to a proper data type was missing.

VARCHAR(...) data type is not a UNIQUEIDENTIFIER data type.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, col UNIQUEIDENTIFIER);
INSERT INTO @tbl (col) VALUES
(TRY_CAST('6B29FC40-CA47-1067-B31D-00DD010662DA' AS UNIQUEIDENTIFIER)),
(TRY_CAST('C49CBB38-2C1E-43DE-8A9D-3DECB6B4F183' AS UNIQUEIDENTIFIER));
-- DDL and sample data population, end

-- Method #1
SELECT * FROM @tbl
WHERE TRY_CAST(col AS VARCHAR(40)) in
(
    '6B29FC40-CA47-1067-B31D-00DD010662DA',
    '7C59FC31-SA87-1667-B31D-00DD010772DA'
);

-- Method #2
SELECT t.*
FROM @tbl AS t INNER JOIN
   (VALUES
      ('6B29FC40-CA47-1067-B31D-00DD010662DA'),
      ('7C59FC31-SA87-1667-B31D-00DD010772DA')
   ) AS UniqueIDs(col)
ON t.col = TRY_CAST(UniqueIDs.col AS UNIQUEIDENTIFIER);