SQL VARIANT causing me to vomit

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL VARIANT causing me to vomit

Author

Topic

shebert
Yak Posting Veteran

85 Posts

Posted - 2009-07-22 : 14:09:09

Helllo
is there a trick to finding guid in a column with data type SQL VARIANT

this query below should return data the uniqueidentifier is in the table i CAN see it if i open the table in ssms.

BUT if I run this in query analyser i get no record...

any ideas?

select * from auditTrail_key where KeyColumnValue ='CBBBDB8B-3B2F-4058-A896-9D93A4FE928D'

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-22 : 14:16:38

select * from auditTrail_key where KeyColumnValue = Convert(uniqueidentifier, 'CBBBDB8B-3B2F-4058-A896-9D93A4FE928D');

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-22 : 14:18:49

Is the value stored in the SQL_VARIANT a UNIQUEIDENTIFIER or a string? I think you have to cast it first:

DECLARE @Foo TABLE (Val SQL_VARIANT)

INSERT @Foo
SELECT NEWID()
UNION ALL SELECT CAST('CBBBDB8B-3B2F-4058-A896-9D93A4FE928D' AS UNIQUEIDENTIFIER)

-- No Results
SELECT *
FROM @foo
WHERE Val = 'CBBBDB8B-3B2F-4058-A896-9D93A4FE928D'

-- Works
SELECT *
FROM @foo
WHERE Val = CAST('CBBBDB8B-3B2F-4058-A896-9D93A4FE928D' AS UNIQUEIDENTIFIER)

EDIT: Dang too slow. :)

shebert
Yak Posting Veteran

85 Posts

Posted - 2009-07-22 : 14:29:33

Thanks
SQL TEAM TO THE RESCUE!!!!!!!!!!!!!!!!!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources